Procedure lem_who2

Diese Prozedure für SQL-Server habe ich schon etwas länger und setzte diese auch gerne ein. Prinzipiell ist es jedoch nur eine Filterfunktion für sp_who2 welches von MS geliefert wird.

use DABB
go

-- Procedure entfernen falls diese noch besteht
				if exists
					(select	*
							from	sys.procedures
							where name 		= 'lem_who2'
							and	schema_id 	= schema_id('dbo'))
					drop procedure dbo.lem_who2

-- Procedure neu erstellen
create procedure [dbo].[lem_who2] 
	@db			varchar(100)	= '%%',
	@user		varchar(100)	= '%%',
	@server		varchar(100)	= '%%',
	@count		bit				= 0,
	@kill		bit				= 0
as
	create table #lem_who2 
		(	SPID		int, 
			status		varchar(1000)	null, 
			Login		sysname			null, 
			HostName	sysname			null, 
			BlkBy		sysname			null, 
			DBName		sysname			null, 
			Command		varchar(1000)	null, 
			CPUTime		int				null, 
			DiskIO		int				null, 
			LastBatch	varchar(1000)	null, 
			ProgramName varchar(1000)	null, 
			SPID2		int, 
			REQUESTID	int);
	insert into #lem_who2 exec sp_who2; 
----------------------------------------------------------------------------------------------------------------------------------------
	if @count	=	1		
		begin
			select	@db			databasename,
					COUNT(*)	cnt
			  from	#iso_who2 
			  where DBName		like @db 
			  and	HostName	like @server 
			  and	Login		like @user;
		end
----------------------------------------------------------------------------------------------------------------------------------------
	if @kill	=	1		
		begin
			select	'kill '+cast(SPID as varchar)+ '		-- '+replace(HostName,' ','')+':'+replace(DBName,' ','')+' ('+Login+')' 
			  from	#lem_who2 
			  where DBName		like @db 
			  and	HostName	like @server 
			  and	Login		like @user;
		end
----------------------------------------------------------------------------------------------------------------------------------------
/*
	if		@count	not in (1) 
		and @kill	not in (1)
		begin 
			create table #lem_who2_cnt 
				(SPID int, status varchar(1000) null, Login sysname null, HostName sysname null, BlkBy sysname null, 
				 DBName sysname null, Command varchar(1000) null, CPUTime int null, DiskIO int null, 
				 LastBatch varchar(1000) null, ProgramName varchar(1000) null, SPID2 int, REQUESTID int);
			insert into #lem_who2_cnt
			select	*																											
			  from	#lem_who2 
			  where DBName		like @db 
			  and	HostName	like @server 
			  and	Login		like @user;
			drop table #lem_who2_cnt;
			select	* 
			  from	#lem_who_cnt;
		end
		*/
----------------------------------------------------------------------------------------------------------------------------------------

			select	*																											
			  from	#lem_who2 
			  where DBName		like @db 
			  and	HostName	like @server 
			  and	Login		like @user;

	drop table #lem_who2;


GO