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 0, 2, NULL
EXEC dbo.sp__who2 1, 2, NULL
-- DECLARE @nSortFlag bit,
-- @nMinAgo int
--
-- SELECT
-- @nSortFlag = 0,
-- @nMinAgo = 1
** Changes
2008-02-19 John Perry
*/
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(1024)) 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())
)
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