A Better sp_who2

If you’ve ever needed to work with sp_who2 to figure out what’s going on, then you know it would be nice to be able to sort the results, right?

Well, if you use the following code, then you’ll get exactly that functionality.  Enjoy!

 

CREATE TABLE #who2 (
   spid INT 
 , [status] SYSNAME 
 , [sid] SYSNAME
 , hostname SYSNAME
 , blocked SYSNAME
 , dbname VARCHAR(50)
 , cmd VARCHAR(300)
 , cpu u
 , physical_io SYSNAME
 , lastbatch VARCHAR(50)
 , [dbid] SYSNAME
 , loginname SYSNAME
 , last_batch_char SYSNAME 
 , programname SYSNAME
 , lastcol INT
)

INSERT INTO    #who2
EXEC sp_who2 

SELECT * 
FROM #who2 
WHERE blocked is not null

DROP TABLE #who2

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *