sp_who2 Alternative (T-SQL, SQL Server)

The trusty "sp_who2" is a quick way to look at what’s happening on your SQL instance, but completely lacks the ability to filter using a WHERE clause. You may have to manually sift through hundreds of results to find what you need.

Never fear.

This quick script gives you the same basic output that sp_who2 gives, except you can throw a filter on the results with the WHERE clause.

Most Like sp_who2

/*Like sp_who2*/
SELECT  spid,
    sp.[status],
    loginame [Login],
    hostname, 
    blocked BlkBy,
    sd.name DBName, 
    cmd Command,
    cpu CPUTime,
    physical_io DiskIO,
    last_batch LastBatch,
    [program_name] ProgramName   
FROM master.dbo.sysprocesses sp 
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
ORDER BY spid

Like sp_who2 With Query Included

/*With SQL text*/
SELECT  sp.spid,
    sp.[status],
    loginame [Login],
    hostname, 
    blocked BlkBy,
    sd.name DBName, 
    cmd Command,
    cpu CPUTime,
    physical_io DiskIO,
    last_batch LastBatch,
    [program_name] ProgramName   , 

    qt.text AS [Parent Query] , sp.cmd
            --, sp.*
FROM master.dbo.sysprocesses sp 
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS qt
/* Don't show this query in results*/
WHERE sp.spid <> @@SPID 
ORDER BY sp.spid

Alternative

/*Another version from JB*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#activity') IS NOT NULL
    DROP TABLE #activity;

IF OBJECT_ID('tempdb..#sp_who2') IS NOT NULL
    DROP TABLE #sp_who2;

CREATE TABLE #sp_who2 (
    SPID INT
    ,STATUS VARCHAR(999)
    ,LOGIN VARCHAR(512)
    ,HostName VARCHAR(512)
    ,BlkBy VARCHAR(999)
    ,DBName VARCHAR(512)
    ,Command VARCHAR(999)
    ,CPUTime BIGINT
    ,DiskIO BIGINT
    ,LastBatch VARCHAR(999)
    ,ProgramName VARCHAR(999)
    ,SPID2 INT
    ,RequestID INT
    );

INSERT #sp_who2
EXECUTE sp_who2 'active';

SELECT w.SPID
    ,STATUS
    ,LOGIN
    ,HostName
    ,BlkBy
    ,DBName
    ,Command
    ,Thrds
    ,CPUTime
    ,DiskIO
    ,LastBatch
    ,ProgramName = CASE 
        WHEN ProgramName LIKE 'SQLAgent - TSQL JobStep (Job %'
            THEN 'SQLAgent - ' + j.name
        ELSE REPLACE(ProgramName, 'Microsoft SQL Server Management Studio', 'SSMS')
        END
    ,event_type
    ,event_info
    ,prm = ib.parameters
    ,ReqID = RequestID
INTO #activity
FROM (
    SELECT SPID
        ,STATUS
        ,LOGIN
        ,HostName
        ,BlkBy = CONVERT(INT, CASE 
                WHEN BlkBy = '  .'
                    THEN 0
                ELSE BlkBy
                END)
        ,DBName
        ,Command
        ,Thrds = COUNT(1)
        ,CPUTime = SUM(CPUTime)
        ,DiskIO = SUM(DiskIO)
        ,LastBatch
        ,ProgramName
        ,RequestID
    FROM #sp_who2 w
    GROUP BY SPID
        ,STATUS
        ,LOGIN
        ,HostName
        ,BlkBy
        ,DBName
        ,Command
        ,LastBatch
        ,ProgramName
        ,RequestID
    ) w
OUTER APPLY sys.dm_exec_input_buffer(w.SPID, w.RequestID) ib
LEFT JOIN (
    SELECT j.name
        ,w.SPID
    FROM #sp_who2 w
    LEFT HASH JOIN msdb.dbo.sysjobs j WITH (NOLOCK) ON master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(16), j.job_id)) = SUBSTRING(REPLACE([ProgramName], 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34)
    WHERE w.ProgramName LIKE 'SQLAgent - TSQL JobStep (Job %'
    ) j ON j.SPID = w.SPID;

SELECT *
FROM #activity
ORDER BY SPID ASC
    ,LOGIN DESC
    ,STATUS ASC;

--
--
/*Blocking Chain*/
IF 1 = 1
BEGIN
    UPDATE ml
    SET ml.LOGIN = l.LOGIN
    FROM #activity ml
    INNER JOIN (
        SELECT b.SPID
            ,b.LOGIN
        FROM #activity b
        WHERE b.LOGIN != ''
        ) l ON l.SPID = ml.SPID
    WHERE ml.LOGIN = '';;

    WITH BLOCKERS (
        SPID
        ,BlkBy
        ,Lvl
        ,LOGIN
        ,event_type
        ,event_info
        )
    AS (
        SELECT SPID
            ,BlkBy
            ,Lvl = CONVERT(VARCHAR(1000), REPLICATE('0', 7 - LEN(CONVERT(VARCHAR, SPID))) + CONVERT(VARCHAR, SPID))
            ,R.LOGIN
            ,R.event_type
            ,R.event_info
        FROM #activity R
        WHERE (
                BlkBy = 0
                OR BlkBy = SPID
                ) /*not blocked; note fix for parallel queries*/
            AND NOT EXISTS (
                SELECT *
                FROM #activity B
                WHERE B.SPID = R.SPID
                    AND B.BlkBy != B.SPID
                    AND B.BlkBy != 0
                ) /*root parallel spid not blocked on another thread*/
            AND EXISTS (
                SELECT *
                FROM #activity B
                WHERE B.BlkBy = R.SPID
                    AND B.BlkBy != B.SPID
                ) /*root spid is blocking other spids*/

        UNION ALL

        SELECT R.SPID
            ,R.BlkBy
            ,Lvl = CONVERT(VARCHAR(1000), B.Lvl + RIGHT(CONVERT(VARCHAR(100), (1000000 + R.SPID)), 7))
            ,R.LOGIN
            ,R.event_type
            ,R.event_info
        FROM #activity AS R
        INNER JOIN BLOCKERS B ON R.BlkBy = B.SPID
        WHERE R.BlkBy > 0
            AND R.BlkBy != R.SPID
        )
    SELECT N'    ' + REPLICATE(N'|         ', LEN(Lvl) / 7 - 1) + CASE 
            WHEN (LEN(Lvl) / 7 - 1) = 0
                THEN 'HEAD -  '
            ELSE '|------  '
            END + CAST(SPID AS NVARCHAR(10)) + N' [' + LOGIN + N'] ' + ISNULL(event_info, '') AS BLOCKING_TREE
    FROM (
        SELECT DISTINCT *
        FROM BLOCKERS
        ) b
    ORDER BY Lvl ASC
        ,SPID ASC;
END;