sp_who2_ext

/*****************************************************************
* A modified version of sp_who2 stored procedure - it allows to *
* filter logins. *
* *
* *
* ver 1.0 (2005.01.26) *
*****************************************************************/

-- DROP PROCEDURE dbo.sp_who2_ext
-- GO

-- the stored procedure
CREATE PROCEDURE dbo.sp_who2_ext
@Login varchar(100) = '%',
@exact bit = 0
AS
CREATE TABLE #table
(
SPID int,
Status varchar(100),
Login varchar(100),
HostName varchar(100),
BlkBy varchar(100),
DBName varchar(100),
Command varchar(100),
CPUTime int,
DiskIO int,
LastBatch varchar(100),
ProgramName varchar(100),
SPID1 int
)

INSERT INTO #table
EXEC dbo.sp_who2

DECLARE @Login_where varchar(102)
IF @exact = 1
SET @Login_where = @Login
ELSE
SET @Login_where = '%' + @Login + '%'

SELECT SPID, Status, Login, HostName, BlkBy, DBName, Command, CPUTime, DiskIO, LastBatch, ProgramName, SPID1
FROM #table
WHERE Login LIKE @Login_where

DROP TABLE #table
GO

-- example usage
EXEC dbo.sp_who2_ext -- this works like a regular sp_who2
EXEC dbo.sp_who2_ext 'marekg' -- this shows logins that contain 'marekg'
EXEC dbo.sp_who2_ext 'marekg', 0 -- ditto
EXEC dbo.sp_who2_ext 'marekg', 1 -- this shows logins that equal 'marekg'
--

Rediscover the web
http://www.mozilla.org/firefox/