Here's my sp_who2 replacement script.
CREATE PROCEDURE dbo.sp__who2 (
@nSortFlag INT = 0,
@nMinAgo INT = NULL,
@nSPID INT = NULL,
@vcDBName VARCHAR(128) = NULL,
@bAll bit = 0
)
AS
/*
** Procedure
** Description
** Parameters
** Requirements
** Usage
EXEC dbo.sp_who2 0, 0
EXEC dbo.sp__who2 0, 0, 84
EXEC dbo.sp__who2 0, NULL, 84
EXEC dbo.sp__who2 NULL, NULL, NULL
EXEC dbo.sp__who2 0, NULL, NULL
EXEC dbo.sp__who2 1, NULL, NULL
EXEC dbo.sp__who2 2, NULL, NULL
EXEC dbo.sp__who2 NULL, 11, NULL, 'Optima'
EXEC dbo.sp__who2 0, 2, NULL
EXEC dbo.sp__who2 1, 2, NULL
EXEC dbo.sp__who2 1, 2, NULL, 'nhpri'
** Changes
2008-02-19 John Perry Removed system processes restriction. OR @nMinAgo IS NULL
2008-08-04 Added memusage.
2008-09-25 Further defined waittype and added active flag and moved it.
*/
DECLARE @bootjobtime datetime
SELECT @bootjobtime = MIN(last_batch)
FROM master.dbo.sysprocesses (nolock)
WHERE spid < 20
SELECT
CAST('A' AS VARCHAR(50)) SortC ,
CAST(0 AS INT) SortN ,
IDENTITY(INT, 1,1) AS ID_Num,
CAST(DB_NAME(dbid) AS VARCHAR(35)) DatabaseName,
spid,
ecid ,
BlkBy = CASE CONVERT(CHAR(5),ISNULL(blocked,'0'))
WHEN '0' THEN ' .'
ELSE CONVERT(CHAR(5),ISNULL(blocked,'0'))
END,
open_tran AS openx,
physical_io AS io,
cpu ,
memusage,
waittime AS waitMS ,
CAST(ISNULL(master_admin.dbo.fn_GetCurrentSQL (sql_handle, stmt_start, stmt_end), cmd) AS VARCHAR(128)) cmd,
hostname,
program_name
,CONVERT(sysname, RTRIM(loginame))AS loginname
,CONVERT(VARCHAR,last_batch,121) AS last_date
, CASE
WHEN waittype = 0x0000 THEN lastwaittype
ELSE '*' + lastwaittype
END WaitType, waitresource
INTO #tmpWHO
FROM master.dbo.sysprocesses (nolock)
WHERE (ISNULL(blocked,'0') > 0
OR
open_tran > 0
)
AND (@nSortFlag IS NOT NULL)
ORDER BY
spid,
ecid
INSERT #tmpWHO (SortC,SortN,
DatabaseName, spid, ecid, BlkBy, openx, io, cpu,
memusage, waitMS, cmd,
hostname, program_name, loginname, last_date, WaitType,
waitresource)
SELECT CASE
WHEN 1=@nSortFlag THEN 'B' + CONVERT(VARCHAR(35),DB_NAME(dbid) )
WHEN 2=@nSortFlag THEN 'B' + CONVERT(VARCHAR(35),last_batch,121)
ELSE 'B'
END
SortC ,
CASE
WHEN 0=0 THEN spid
ELSE 99
END SortN ,
CAST(DB_NAME(dbid) AS VARCHAR(35)) DatabaseName,
spid,
ecid ,
BlkBy = CASE CONVERT(CHAR(5),ISNULL(blocked,'0'))
WHEN '0' THEN ' .'
ELSE CONVERT(CHAR(5),ISNULL(blocked,'0'))
END,
open_tran AS openx,
physical_io AS io,
cpu ,
memusage,
waittime AS waitMS ,
cmd,
hostname,
program_name
,CONVERT(sysname, RTRIM(loginame))AS loginname
,CONVERT(VARCHAR,last_batch,121) AS last_date
, CASE
WHEN waittype = 0x0000 THEN lastwaittype
ELSE '*' + lastwaittype -- (active)
END WaitType, waitresource
FROM master.dbo.sysprocesses (nolock)
WHERE (
(@nSortFlag IS NULL)
OR
(ISNULL(blocked,'0') = 0
AND
open_tran = 0)
)
AND
(@nSPID > 0
OR @nMinAgo IS NULL
OR last_batch >= DATEADD(minute, -@nMinAgo, GETDATE()))
-- ORDER BY
-- SortCol,
-- ecid
SELECT DatabaseName, spid, ecid, BlkBy, openx, io, cpu,
memusage, waitMS, WaitType,
cmd,
hostname, program_name, loginname, last_date,
waitresource
--, SortC,SortN,id_NUM
FROM #tmpWHO
WHERE
(@bAll = 1
OR last_date > @bootjobtime
OR @nMinAgo IS NULL )
AND (@vcDBName IS NULL
OR DatabaseName LIKE '%' + @vcDBName + '%')
AND (@nSPID IS NULL
OR @nSPID = 0
OR spid = @nSPID)
ORDER BY
SortC,SortN,
spid,
ecid
DROP TABLE #tmpWHO
GO