use master
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_kill_db'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.sp_kill_db
    IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_kill_db'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_kill_db >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_kill_db >>>'
END
go
create procedure sp_kill_db
(
        @dbname nvarchar( 128 ) = NULL
)
as
--WITH ENCRYPTION
/*********************************************************************************
 * $History: $
 *********************************************************************************/
if @dbname is null
begin
        raiserror( 'Please specify a database from which connections have to be killed.' , 1 , 2 )
        return( 1 )        
end
declare @spid varchar(10), @start datetime
select @start = current_timestamp
-- Timeout after 5 mts
while( exists( select * from sysprocesses
               where dbid = db_id(@dbname) ) and
       datediff(mi, @start, current_timestamp) < 5 )
begin
        declare spids cursor for select convert(varchar, spid) from sysprocesses
                                 where dbid = db_id(@dbname)
        open spids
        while(1=1)
        begin
                fetch spids into @spid
                if @@fetch_status < 0 break
                exec('kill ' + @spid)
        end
        deallocate spids
end
if exists(Select * from sysprocesses where dbid = db_id(@dbname))
begin
        raiserror( 'Some processes are still using the database.
Please review the accompanying output.' , 18 , 1 )
        select spid, cmd, status, open_tran, blocked , lastwaittype , last_batch, 
               program_name, hostname
        from sysprocesses
        where spid > 6 and dbid = db_id(@dbname)
        return( -1 )
end
return( 0 )
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_kill_db'), 'IsProcedure') = 1
BEGIN
     GRANT EXECUTE ON dbo.sp_kill_db To Public
     PRINT '<<< CREATED PROCEDURE dbo.sp_kill_db >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_kill_db >>>'
go

This page was last updated on May 01, 2006 04:28 PM.