Earlier this week, I discussed the method of representing sp_who2 with DMVs in SQL Server. Many readers requested that I also include the script for the alternative method mentioned in the blog post. So, in this article, I will explain how to insert sp_who2 results into a table.

To begin, let’s create a temporary table called #sp_who2 with the necessary columns:

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),
    SPID1 INT,
    REQUESTID INT
);

Next, we can insert the results of sp_who2 into the temporary table:

INSERT INTO #sp_who2
EXEC sp_who2;

If you want to view the inserted data, you can simply select all rows from the temporary table:

SELECT *
FROM #sp_who2
-- WHERE DBName <> 'master' -- Add Filter
ORDER BY SPID ASC;

Finally, don’t forget to drop the temporary table once you’re done:

DROP TABLE #sp_who2;

By using the above script, you can easily insert the results of sp_who2 into a table and apply any necessary filters or sorting to obtain the desired results. Additionally, you can store the values in a table for a longer period and analyze them later on.

While inserting sp_who2 results into a table is a straightforward approach, I personally prefer using DMVs for this purpose. In my Comprehensive Database Performance Health Check, I find that using DMVs provides more flexibility and detailed information.

If you’re interested in learning more about SQL Server performance tuning, I recommend checking out the following recent blog posts on the same topic:

• Slow Running Query – SQL in Sixty Seconds #146
• Sleeping vs Suspended Process – SQL in Sixty Seconds #122
• Recent Execution of Stored Procedure – SQL in Sixty Seconds #118

I hope you found this blog post helpful. If you have any feedback or suggestions, please let me know. If you have a similar script that you think would be useful to other users, feel free to share it with me, and I’ll be happy to give you credit by posting it on the blog.