SQL Server: Filter output of sp_who2:

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:

sp_who2 [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]

Permissions needed for sp_who2

A login can run sp_who2 and obtain information about its own connection. For a full list of SPID’s, either the login has to have sysadmin permission or VIEW SERVER STATE permission.

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 ....