• 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