SQL Server: Filter output of sp_who2

Profile picture for Craig Schwarze
Craig Schwarze

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.

You could save the results into a temp table, but it would be even better to go directly to the source on master.dbo.sysprocesses.

Here's a query that will return almost the exact same result as sp_who2:

<!-- language: lang-sql --> 
SELECT  spid,
        sp.[status],
        loginame [Login],
        hostname, 
        blocked BlkBy,
        sd.name DBName, 
        cmd Command,
        cpu CPUTime,
        physical_io DiskIO,
        last_batch LastBatch,
        [program_name] ProgramName   
FROM master.dbo.sysprocesses sp 
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
ORDER BY spid

Now you can easily add any ORDER BY or WHERE clauses you like to get meaningful output.

Alternatively, you might consider using Activity Monitor in SSMS (<kbd>Ctrl</kbd> + <kbd>Alt</kbd> + <kbd>A</kbd>) as well