As a DBA sp_who2 is a stored procedure I use on a daily basis, however, it is not possible to filter its output or manipulate it in any way.
To help with this I use the simple script below as a snippet that exports the output of sp_who2 to a temporary table where I can play with the results much easier.
To help with this I use the simple script below as a snippet that exports the output of sp_who2 to a temporary table where I can play with the results much easier.
-- Export sp_who2 to temp table
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#spwho2')) DROP TABLE #spwho2
CREATE TABLE #spwho2(
[SPID] INT
,[Status] VARCHAR(30)
,[Login] VARCHAR(50)
,[HostName] VARCHAR(30)
,[BlkBy] VARCHAR(30)
,[DBName] VARCHAR(30)
,[Command] VARCHAR(50)
,[CPUTime] INT
,[DiskIO] INT
,[LastBatch] VARCHAR(30)
,[ProgramName] VARCHAR(100)
,[SPID2] INT
,[RequestID] INT
)
INSERT INTO #spwho2
EXEC sp_who2
SELECT [SPID]
,[Status]
,[Login]
,[HostName]
,[BlkBy]
,[DBName]
,[Command]
,[CPUTime]
,[DiskIO]
,[LastBatch]
,[ProgramName]
,[SPID2]
,[RequestID]
FROM #spwho2
Comments
Post a Comment