How to sort data pulled from sp_who2

Posted on Updated on

— using a temporary table

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,
REQUESTID INT)
INSERT INTO #sp_who2 EXEC sp_who2
SELECT      *
FROM        #sp_who2
— Add any filtering of the results here :
WHERE       DBName = ‘<database name’
— Add any sorting of the results here :
ORDER BY    DBName ASC

DROP TABLE #sp_who2

— using a table variable

declare @tempTable table (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,
REQUESTID INT);

INSERT INTO @tempTable
EXEC sp_who2

select *
from @tempTable
where DBName = ‘<database name’

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s