SQL Server : sp_who2 - filtering and sorting the results for connections

The stored procedure sp_who2 lists all current processes connected to a SQL Server :

exec sp_who2

A typical resultset from Management Studio is :




sp_who2 is one of the most useful and widely used stored procedures, along with its predecessor sp_who. However it is also one of the most frustrating as it only takes a single parameter and the results cannot be ordered. For a large server with a lot of connections this can be a real nuisance. I usually store the results in a temporary table and then filter and/or order the results from there :

CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255), 
      BlkBy  VARCHAR(255),DBName  VARCHAR(255), 
      Command VARCHAR(255),CPUTime INT, 
      DiskIO INT,LastBatch VARCHAR(255), 
      ProgramName VARCHAR(255),SPID2 INT, 
      REQUESTID INT) 
INSERT INTO #sp_who2 EXEC sp_who2
SELECT      * 
FROM        #sp_who2
-- Add any filtering of the results here :
WHERE       DBName <> 'master'
-- Add any sorting of the results here :
ORDER BY    DBName ASC
 
DROP TABLE #sp_who2

Some people encapsulate the above code in a stored procedure and run that, but my preference is always to run it as a script.

Finally

A word of warning. Sp_who2 is undocumented, meaning that Microsoft could change it in the future without warning. I've tested the code above on SQL Server 2005, 2008 and 2008 R2, however it's possible that the columns or datatypes returned could change in future versions which would require a small change in the code.