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,
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.