Home | Catalogue | Robert's Blog
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(
INT,
SPID VARCHAR(MAX),
Status VARCHAR(MAX),
LOGIN VARCHAR(MAX),
HostName VARCHAR(MAX),
BlkBy VARCHAR(MAX),
DBName VARCHAR(MAX),
Command INT,
CPUTime INT,
DiskIO VARCHAR(MAX),
LastBatch VARCHAR(MAX),
ProgramName INT,
SPID_1 INT
REQUESTID
)
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.