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