I call it smart sp_who2

If you have been working on SQL Server, you would have definitely used sp_who2. One of the limitations of sp_who2 is that you cannot filter the result set directly and we all know how frustrating that can be sometimes. Obviously there are DMVs you can use to get similar information, but if you are like me and like to use the output of sp_who2 to join with other tables and so on , here a way to filter and query the sp_who2. Basically you create a temporary table and insert the output of sp_who2 to the table and Viola ! Here is how :

DECLARE @Table TABLE(
SPID INT,
Status VARCHAR(MAX),
LOGIN VARCHAR(MAX),
HostName VARCHAR(MAX),
BlkBy VARCHAR(MAX),
DBName VARCHAR(MAX),
Command VARCHAR(MAX),
CPUTime INT,
DiskIO INT,
LastBatch VARCHAR(MAX),
ProgramName VARCHAR(MAX),
SPID_1 INT,
REQUESTID INT
);

INSERT INTO @Table EXEC sp_who2;

SELECT *
FROM @Table
WHERE  login = ‘raju’
order by DBName;

Now you can join the @table with any other system catalog to get more info or trend patterns.