SQL Server: Filter output of sp_who2

Question

Under SQL Server, is there an easy way to filter the output of sp_who2? Say I wanted to just show rows for a certain database, for example.

Answer 1

You could try something like

DECLARE @Table TABLE(
        SPID INT,
        Status VARCHAR(MAX),
        LOGIN VARCHAR(MAX),
        HostName VARCHAR(MAX),
        BlkBy VARCHAR(MAX),
        DBName VARCHAR(MAX),
        Command VARCHAR(MAX),
        CPUTime INT,
        DiskIO INT,
        LastBatch VARCHAR(MAX),
        ProramName VARCHAR(MAX),
        SPID_1 INT,
        REQUESTID INT
)

INSERT INTO @Table EXEC sp_who2

SELECT  *
FROM    @Table
WHERE ....

And filter on what you require.

Answer 2

One way is to create a temp table:

CREATE TABLE #sp_who2 
(
   SPID INT,  
   Status VARCHAR(1000) NULL,  
   Login SYSNAME NULL,  
   HostName SYSNAME NULL,  
   BlkBy SYSNAME NULL,  
   DBName SYSNAME NULL,  
   Command VARCHAR(1000) NULL,  
   CPUTime INT NULL,  
   DiskIO INT NULL,  
   LastBatch VARCHAR(1000) NULL,  
   ProgramName VARCHAR(1000) NULL,  
   SPID2 INT
) 
GO

INSERT INTO #sp_who2
EXEC sp_who2
GO

SELECT *
FROM #sp_who2
WHERE Login = 'bla'
GO

DROP TABLE #sp_who2
GO

Answer 3

Yes, by capturing the output of sp_who2 into a table and then selecting from the table, but that would be a bad way of doing it. First, because sp_who2, despite its popularity, its an undocumented procedure and you shouldn't rely on undocumented procedures. Second because all sp_who2 can do, and much more, can be obtained from sys.dm_exec_requests and other DMVs, and show can be filtered, ordered, joined and all the other goodies that come with queriable rowsets.

Answer 4

There's quite a few good sp_who3 user stored procedures out there - I'm sure Adam Machanic did a really good one, AFAIK.

Adam calls it Who Is Active: http://sqlblog.com/blogs/adam_machanic/archive/tags/Scripts/default.aspx