The Chalkboard

Robert Hawdon's Knowledge Base

Finding processes accessing a database in T-SQL (MSSQL)

Last Updated: [2023-03-30 Thu 02:10]

The EXEC sp_who2 SQL statement is well known, however it's impossible to search as it's not exactly a "real" SQL statement. Here, we're going to define a temporary table as a variable, load the data from EXEC sp_who2 into it, and run a query against that. The variable is only valid for that query, which is perfect for this use case.

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   DBName = ''

In this case, we can search for a specific Database.

DISCLAIMER: The information provided on this website is generated from my own notes and is provided "as is" and without warranties. Robert Ian Hawdon can not be held responsible for damages caused by following a guide published on this site. This website contains links to other third-party websites. Such links are provided as convienice of the reader. I do not endorce the contents of these third party sites.