I'm digging this solution. I really like to be able to filter my results, so I took the code from Dan (nice code Dan) that used the trick from Adam Machanic, and turned it into a table valued function:
USE [master]
GO
/****** Object: UserDefinedFunction [dbo].[fn_sp_who4] Script Date: 06/26/2008 11:31:07 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_sp_who4]
(@filterspid int = NULL, @filter tinyint = 1)
RETURNS @processes TABLE
(
spid int,
blocked int,
databasename varchar(256),
hostname varchar(256),
current_statement_parent xml,
current_statement_sub xml,
program_name varchar(256),
loginame varchar(256),
status varchar(60),
cmd varchar(128),
cpu int,
physical_io int,
[memusage] int,
login_time datetime,
last_batch datetime
)
AS
BEGIN
INSERT INTO @processes
SELECT sub.*
FROM
(SELECT sp.spid,
sp.blocked,
sd.name,
RTRIM(sp.hostname) AS hostname,
( SELECT LTRIM(st.text) AS [text()]
FOR XML PATH(''), TYPE) AS parent_text,
( SELECT
LTRIM(CASE
WHEN LEN(COALESCE(st.text, '')) = 0 THEN NULL
ELSE SUBSTRING(st.text, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1)
END) AS [text()]
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
WHERE er.session_id = sp.spid
FOR XML PATH(''), TYPE) AS child_text,
RTRIM(sp.[program_name]) AS [program_name],
RTRIM(sp.loginame) AS loginame,
RTRIM(sp.status) AS status,
sp.cmd,
sp.cpu,
sp.physical_io,
sp.memusage,
sp.login_time,
sp.last_batch
FROM sys.sysprocesses sp (NOLOCK)
LEFT JOIN sys.sysdatabases sd WITH (NOLOCK) ON sp.dbid = sd.dbid
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS st
) sub
INNER JOIN sys.sysprocesses sp2 ON sub.spid = sp2.spid
ORDER BY sub.spid
-- if specific spid required
IF @filterspid IS NOT NULL
DELETE @processes
WHERE spid <> @filterspid
-- remove system processes
IF @filter = 1 OR @filter = 2
DELETE @processes
WHERE spid < 51 OR spid = @@SPID
-- remove inactive processes
IF @filter = 2
DELETE @processes
WHERE status = 'sleeping'
AND cmd IN ('AWAITING COMMAND')
AND blocked = 0
RETURN
END
Then you can run
SELECT ... from master.dbo.fn_sp_who4(@filterspid = , @filter = )
WHERE ...
or
SELECT * from master.dbo.fn_sp_who4(default,default) for the function parameter defaults
You could also technically remove the parameters entirely since it's a table valued function after all and just use the where clause in the SELECT.
It's rough, but I like it 😀
--John Vanda
SQL Junior DBA