Adding filters and sorts in SP_WHO2

Have you ever wished if you could have somehow filtered the results from our all time favorite command SP_WHO2 instead of going through hundreds of rows of data.

If you copied the results to excel and used sorting and filtering in excel , you are not alone.

For those who are not aware of  SP_WHO2 ‘Active’, please give it a try. I use it most of the time.

Here is a script using which you can filter or sort the result from SP_WHO2.

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

If you use a particular filter all the time, you can set a constant filter and save it as stored procedure e.g. SP_Login_XYZ filtered for a login XYZ.

About: Niraj Kumar


Leave a Reply

Your email address will not be published. Required fields are marked *