usp_KillConnections

/*****************************************************************
*** Procedure: usp_KillConnections 

*** Usage: usp_KillConnections @dbname = 'Database Name'

*** Description: Drop all connections from a specific database

*** Input: @dbname - REQUIRED - Name of the database
*** Output: Outputs the results of the proccess

*** Revision: 1.0 
*** Revision History: 1.0 First Release
*** Author: Antonio Pedrosa Linares
*** Date: 7/25/2007
******************************************************************/
create procedure usp_KillConnections
@dbname varchar(128)
as
declare @spid varchar(5)
declare @loginname nvarchar(128)
declare @intErrorCode int
declare @intOk int
declare @intError int
declare @intTotal int

set @intErrorCode = 0
set @intOk = 0
set @intError = 0
set @intTotal = 0

select @intTotal = count(sp.spid) FROM master..sysprocesses sp
JOIN master..sysdatabases sd ON sp.dbid = sd.dbid
WHERE sd.name = @dbname

declare KILL_CONS cursor for
SELECT cast(sp.spid as varchar(5)),rtrim(sp.loginame)
FROM master..sysprocesses sp
JOIN master..sysdatabases sd ON sp.dbid = sd.dbid
WHERE sd.name = @dbname

OPEN KILL_CONS

FETCH NEXT FROM KILL_CONS INTO @spid,@loginname
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('Kill '+ @spid + '')
SELECT @intErrorCode = @@ERROR
if @intErrorCode = 0
begin
set @intOk = @intOk + 1
PRINT 'Process ' + @spid + ' from login ' + @loginname + ' has been ended.'
end
else
begin
set @intError = @intError + 1
PRINT 'Process ' + @spid + ' from login ' + @loginname + ' could not be ended.'
end
FETCH NEXT FROM KILL_CONS INTO @spid,@loginname
END
CLOSE KILL_CONS
DEALLOCATE KILL_CONS
PRINT 'Total number of processes from database ' + @dbname + ': ' + cast(@intTotal as varchar)
PRINT 'Processes ended normally: ' + cast(@intOk as varchar)
PRINT 'Processes could not be ended: ' + cast(@intError as varchar)
GO