Filtering sp_who

sp_who – useful – up to a point. Particularly when the server is busy, and you’re looking for something specific (eg to see if certain processes are out of a database before running an update)

If the server is busy – don’t you wish there was a way to run something like

sp_who WHERE dbname = 'foo'

Yeah. Unfortunately, it doesn’t work like that. What you have to do instead is trap the output from sp_who into a temporary table or table variable, and query that. eg:

DECLARE @spwho TABLE
(
    spid SMALLINT,
    ecid SMALLINT,
    status NVARCHAR(30),
    loginame NVARCHAR(128),
    hostname NVARCHAR(128),
    blk VARCHAR(5),
    dbname NVARCHAR(128),
    cmd NVARCHAR(16),
    request_id INT
);

INSERT INTO @spwho
EXEC sys.sp_who;

SELECT *
FROM @spwho WHERE --insert WHERE clause here

I usually have that as a SQLPrompt snippet “spwhofilter”, with the addition of $CURSOR$ just after the WHERE so that I can start typing immediately something like “spwhofilter dbname LIKE ‘Foo%'”

Disclaimer: Yes, I’m a friend of Redgate. No, I don’t get paid, but I do get to use their software. And, frankly, SQLPrompt is one that I would not hesitate to pay for myself, as it could save your career.