The stored procedure sp_who2 lists all current processes connected to a SQL Server :
exec sp_who2
A typical resultset from Management Studio is :
sp_who2 is one of the most useful and widely used stored procedures, along with its predecessor sp_who.
However it is also one of the most frustrating as it only takes a single parameter and the results cannot
be ordered. For a large server with a lot of connections this can be a real nuisance. I usually store the
results in a temporary table and then filter and/or order the results from there :
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
<> 'master'
-- Add any sorting of the results here :
ORDER BY DBName ASC
DROP TABLE #sp_who2
Some people encapsulate the above code in a stored procedure and run that, but my preference is always to
run it as a script.
Finally
A word of warning. Sp_who2 is undocumented, meaning that Microsoft could change it in the future
without warning. I’ve tested the code above on SQL Server 2005, 2008 and 2008 R2, however it’s possible
that the columns or datatypes returned could change in future versions which would require a small change in the
code.