During a recent Comprehensive Database Performance Health Check, I encountered an interesting situation where a client wanted to capture the details of the sp_who2 stored procedure in a table. In this blog post, I will explain how we can achieve this using Dynamic Management Views (DMVs).

sp_who2 is an undocumented stored procedure that displays the current session IDs and their activities. While sp_who2 is generally user-friendly and easy to use, storing its data in a table is not straightforward. However, there are two workarounds to this problem.

Workaround 1: Manual Creation of Temporary Table

The first workaround involves manually creating a temporary table and running sp_who2 frequently to store the data in the table. However, many users find this approach cumbersome and not very straightforward.

Workaround 2: Using DMVs

The second alternative, which is my preferred method, involves using DMVs to represent sp_who2. By using the following script, we can achieve similar results as sp_who2:

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.sys.sysprocesses sp 
INNER JOIN master.sys.sysdatabases sd ON sp.dbid = sd.dbid
WHERE spid > 50 -- Filtering System spid
ORDER BY spid

The only difference between this script and sp_who2 is the WHERE condition, which filters out all the system sp_id with a value less than 50. You can further customize the query by adding additional filters or columns to suit your specific needs.

If you want to store the data directly into a table, you can use the INTO #TempTable command between the FROM and the first INNER JOIN.

I hope you find this blog post helpful. If you have any feedback or suggestions, please let me know. If you have any similar scripts that you think would be useful to other users, feel free to share them with me, and I will be happy to post them on the blog with proper credit to you.