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