sp_who2 filtering

sp_who2_filter.sql

/*
	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