sp_kill

use master
go

if exists (select name from sysobjects where name = 'sp_kill' and type = 'p' )
    drop proc sp_kill
go

create proc sp_kill 
		@dbname varchar(100) = null, 		--	When specified, kills all spids inside of the database
		@loginame varchar(50) = null, 	--	When specified, kills all spids under the login name
		@hostname varchar(50) = null		--	When specified, kills all spids originating from the host machine
as
begin
	set nocount on
	select spid, db_name(dbid) as 'db_name', loginame, hostname into #tb1_sysprocesses from master.dbo.sysprocesses (nolock)
	declare @total_logins int, @csr_spid varchar(100)		
	set @total_logins = ( select count(distinct spid) from #tb1_sysprocesses )
	if @dbname is null
	begin
		if @loginame is null
		begin
			if @hostname is null
			begin
				if @total_logins > 0
				begin 
				declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where loginame <> 'sa' and spid <> @@spid
				open csr_spid	
				fetch next from csr_spid into @csr_spid
				while @@fetch_status = 0
					begin
						set nocount on     
						exec ('kill ' + @csr_spid)
						fetch next from csr_spid into @csr_spid
					end
				close csr_spid
				deallocate csr_spid
				end
			end
			else
			begin
				if @total_logins > 0
				begin 
				declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where hostname = @hostname  and loginame <> 'sa' and spid <> @@spid
				open csr_spid	
				fetch next from csr_spid into @csr_spid
				while @@fetch_status = 0
					begin
						set nocount on     
						exec ('kill ' + @csr_spid)
						fetch next from csr_spid into @csr_spid
					end
				close csr_spid
				deallocate csr_spid
				end
			end
--------------------------------------------------
		end				
		else
		begin
				if @total_logins > 0
				begin 
				declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where loginame = @loginame and  loginame <> 'sa' and spid <> @@spid
				open csr_spid	
				fetch next from csr_spid into @csr_spid
				while @@fetch_status = 0
					begin
						set nocount on     
						exec ('kill ' + @csr_spid)
						fetch next from csr_spid into @csr_spid
					end
				close csr_spid
				deallocate csr_spid
				end
			
		end
-----------------------
	end
	else
	begin
				if @total_logins > 0
				begin 
				declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where db_name = @dbname  and loginame <> 'sa' and spid <> @@spid
				open csr_spid	
				fetch next from csr_spid into @csr_spid
				while @@fetch_status = 0
					begin
						set nocount on     
						exec ('kill ' + @csr_spid)
						fetch next from csr_spid into @csr_spid
					end
				close csr_spid
				deallocate csr_spid
				end

	end
	drop table #tb1_sysprocesses
end