Skip to content

Instantly share code, notes, and snippets.

@Laim

Laim/sp_who2_filter.sql

Last active May 7, 2021
Embed
What would you like to do?
sp_who2 filtering
/*
Author: Laim McKenzie
Date: 30-04-2021
Updated: 07-05-2021
https://laim.scot
*/
/* Create temp table to populate with data */
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 data into temp table */
INSERT INTO #sp_who2 EXEC sp_who2
/* Select statement, basic stuff really */
SELECT
SPID
,Status
,Login
,HostName
,BlkBy
,DBName
,Command
,CPUTime
,CONVERT(NUMERIC(32, 2), CPUTime / 1000.0 / 60.0) AS [CPUTime (Minutes)]
,CONVERT(NUMERIC(32, 2), CPUTime / 1000.0 / 60.0 / 60.0) AS [CPUTime (Hours)]
,DiskIO
,CONVERT(NUMERIC(32,2), DiskIO / 1048576) AS [DiskIO (Megabytes)]
,LastBatch
,ProgramName
,SPID2
,RequestID
FROM #sp_who2
WHERE DBName <> 'master'
ORDER BY DBName ASC
/* Drop the temp table */
DROP TABLE #sp_who2
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment