-- http://www.sqlservercentral.com/scripts/sp_who/68607/
--exec msdb.dbo.usp_who5 'A'
--exec msdb.dbo.usp_who5 'B'
--exec msdb.dbo.usp_who5 'X'
--exec msdb.dbo.usp_who5 'I?'
--exec msdb.dbo.usp_who5 'O?'

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

----------------------------------------------------------------------------------------------------------------------
--	Error Trapping: Check If Procedure Already Exists And Drop If Applicable
----------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID ('[dbo].[usp_who5]') IS NOT NULL
BEGIN
	DROP PROCEDURE [dbo].[usp_who5]
END
GO


----------------------------------------------------------------------------------------------------------------------
--	Stored Procedure Details: Listing Of Standard Details Related To The Stored Procedure
----------------------------------------------------------------------------------------------------------------------

-- Purpose: Return Information Regarding Current Users / Sessions / Processes On A SQL Server Instance
-- Create Date: 10/27/2009
-- Created By: Sean Smith (s(DOT)smith(DOT)sql(AT)gmail(DOT)com)
-- Modifications: 11/05/2009 - Converted Script To Dynamic-SQL


----------------------------------------------------------------------------------------------------------------------
--	Main Query: Create Procedure
----------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[usp_who5]
	 @vFilter_Active_Blocked_System AS VARCHAR (5) = NULL
	,@vFilter_SPID AS SMALLINT = NULL
	,@vFilter_NT_Username_Or_Loginame AS NVARCHAR (128) = NULL
	,@vFilter_SQL_Statement AS NVARCHAR (4000) = NULL
--	,@vFilter_SQL_Statement AS NVARCHAR (MAX) = NULL	-- NOT COMPATIBLE IN SQL 2000
AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON


----------------------------------------------------------------------------------------------------------------------
--	Error Trapping: Check If "@vFilter_Active_Blocked_System" Parameter Is An Input/Output Help Request
----------------------------------------------------------------------------------------------------------------------

IF @vFilter_Active_Blocked_System = 'I?'
BEGIN
	RAISERROR
		(
			'INPUT INFO...'
-- NOT COMPATIBLE IN SQL 2000 = string too long
/*			 '
Syntax:
	EXEC dbo.usp_who5

Optional Input Parameters:
	@vFilter_Active_Blocked_System   : Limit result set by passing one or more values listed below (can be used individually or combined in any manner):
		A - Active SPIDs Only
		B - Blocked SPIDs Only
		X - Exclude System Reserved SPIDs (1-50)
	@vFilter_SPID                    : Limit result set to a specific SPID
	@vFilter_NT_Username_Or_Loginame : Limit result set to a specific Windows user name (if populated), otherwise by SQL Server login name
	@vFilter_SQL_Statement           : Limit result set to SQL statement(s) containing specific text
Notes:
	Blocked SPIDs (Blocked / Blocking / Parallelism) will always be displayed first in the result set
			 '
*/
			,16
			,1
		)

	GOTO skip_query
END


IF @vFilter_Active_Blocked_System = 'O?'
BEGIN
	RAISERROR
		(
			'OUTPUT INFO...'
-- NOT COMPATIBLE IN SQL 2000 = string too long
/*			 '
Output:
	SPECID                  : System Process ID with Execution Context ID
	Blocked                 : Blocking indicator (includes type of block and blocking SPID)
	Running                 : Indicates if SPID is currently executing, waiting, inactive, or has open transactions
	Login_ID                : Displays Windows user name (or login name if user name is unavailable)
	Login_Name              : Full name of the user associated to the Login_ID (if available)
	Elapsed_Time            : Total elapsed time since the request began (format HH:MM:SS)
	CPU_Total               : Cumulative CPU time since SPID login (format HH:MM:SS)
	CPU_Current             : Cumulative CPU time for currently executing request (format HH:MM:SS)
	Logical_Reads           : Number of logical reads performed by the current process
	Physical_Reads          : Number of physical reads performed by the current process
	Writes                  : Number of writes performed by the current process
	Pages_Used              : Number of pages in the procedure cache currently allocated to this process
	Nesting_Level           : Nesting level of the statement currently being executed
	Open_Trans              : Number of open transactions for the process
	Wait_Time               : Current wait time (format HH:MM:SS)
	Status                  : Status of the current process
	Command                 : Command currently being executed
	SQL_Statement           : Returns the SQL statement of the associated SPID
	Since_SPID_Login        : Total elapsed time since the client logged into the server (format HH:MM:SS)
	Since_Last_Batch        : Total elapsed time since the client last completed a remote stored procedure call or an EXECUTE statement (format HH:MM:SS)
	Workstation_Name        : Workstation name
	Database_Name           : Database context of the SPID
	Application_Description : Application accessing SQL Server
	SPECID                  : System Process ID with Execution Context ID
			 '
*/
			,16
			,1
		)

	GOTO skip_query
END


----------------------------------------------------------------------------------------------------------------------
--	Declarations/Sets: Declare And Set Variables
----------------------------------------------------------------------------------------------------------------------

DECLARE @vFilter_Active AS BIT
DECLARE @vFilter_Blocked AS BIT
DECLARE @vFilter_System AS BIT
DECLARE @vSQL_String AS VARCHAR (4000)
--DECLARE @vSQL_String AS VARCHAR (MAX)		-- NOT COMPATIBLE IN SQL 2000

SET @vFilter_NT_Username_Or_Loginame = NULLIF (@vFilter_NT_Username_Or_Loginame,'')
SET @vFilter_SQL_Statement = NULLIF (REPLACE (@vFilter_SQL_Statement,'''',''''''),'')
SET @vFilter_Active = (CASE WHEN @vFilter_Active_Blocked_System LIKE '%A%' THEN 1 ELSE 0 END)
SET @vFilter_Blocked = (CASE WHEN @vFilter_Active_Blocked_System LIKE '%B%' THEN 1 ELSE 0 END)
SET @vFilter_System = (CASE WHEN @vFilter_Active_Blocked_System LIKE '%X%' THEN 1 ELSE 0 END)


----------------------------------------------------------------------------------------------------------------------
--	Main Query: Final Display/Output
----------------------------------------------------------------------------------------------------------------------
print '1 vSQL_String===='
print @vSQL_String
print LEN(@vSQL_String)

SET @vSQL_String =
	'
		SELECT
			 CONVERT (VARCHAR (6), SP.spid)+''.''+CONVERT (VARCHAR (6), SP.ecid)+(CASE WHEN SP.spid = @@SPID THEN '' •••'' ELSE '''' END) AS SPECID
			,(CASE
				WHEN SP.blocked = 0 AND Y.blocked IS NULL THEN ''·············''
				WHEN SP.blocked = SP.spid THEN ''> Parallelism <''
				WHEN SP.blocked = 0 AND Y.blocked IS NOT NULL THEN ''>> BLOCKING <<''
				ELSE ''SPID: ''+CONVERT (VARCHAR (6), B.spid)+''  •  ''+(CASE
														WHEN B.Login_ID_Blocking = ''sa'' THEN ''<< System Administrator >>''
														ELSE ISNULL (B.Login_ID_Blocking,''N/A'')
														END)
				END) AS Blocked
			,(CASE
				WHEN SP.spid <= 50 THEN ''     --''
				WHEN SP.status IN (''dormant'',''sleeping'') AND SP.open_tran = 0 THEN ''''
				WHEN SP.status IN (''dormant'',''sleeping'') THEN ''     •''
				WHEN SP.status IN (''defwakeup'',''pending'',''spinloop'',''suspended'') THEN ''     *''
				ELSE ''     X''
				END) AS Running
			,ISNULL (NULLIF (SP.nt_username,''''),SP.loginame) AS Login_ID
			,ISNULL ((CASE
					WHEN SP.loginame = ''sa'' THEN ''<< System Administrator >>''
					ELSE SP.loginame
					END),'''') AS Login_Name
			,(CASE
				WHEN SP.spid >= 51 AND LEN ((DMER.total_elapsed_time/1000)/3600) > 2 THEN ''99:99:99+''
				WHEN SP.spid >= 51 THEN ISNULL (RIGHT (''00''+CONVERT (VARCHAR (2), (DMER.total_elapsed_time/1000)/3600),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((DMER.total_elapsed_time/1000)%3600)/60),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((DMER.total_elapsed_time/1000)%3600)%60),2),'''')
				ELSE ''''
				END) AS Elapsed_Time
			,(CASE
				WHEN SP.cpu = 0 THEN ''''
				WHEN LEN ((SP.cpu/1000)/3600) > 2 THEN ''99:99:99+''
				ELSE RIGHT (''00''+CONVERT (VARCHAR (2), (SP.cpu/1000)/3600),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((SP.cpu/1000)%3600)/60),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((SP.cpu/1000)%3600)%60),2)
				END) AS CPU_Total
			,(CASE
				WHEN DMER.cpu_time = 0 THEN ''''
				WHEN LEN ((DMER.cpu_time/1000)/3600) > 2 THEN ''99:99:99+''
				ELSE ISNULL (RIGHT (''00''+CONVERT (VARCHAR (2), (DMER.cpu_time/1000)/3600),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((DMER.cpu_time/1000)%3600)/60),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((DMER.cpu_time/1000)%3600)%60),2),'''')
				END) AS CPU_Current
			,ISNULL (CONVERT (VARCHAR (20), DMER.logical_reads),'''') AS Logical_Reads
			,ISNULL (CONVERT (VARCHAR (20), DMER.reads),'''') AS Physical_Reads
			,ISNULL (CONVERT (VARCHAR (20), DMER.writes),'''') AS Writes
			,(CASE
				WHEN SP.memusage = 0 THEN ''''
				ELSE CONVERT (VARCHAR (10), SP.memusage)
				END) AS Pages_Used
			,ISNULL (CONVERT (VARCHAR (15), DMER.nest_level),'''') AS Nesting_Level
			,(CASE
				WHEN SP.open_tran = 0 THEN ''''
				ELSE CONVERT (VARCHAR (10), SP.open_tran)
				END) AS Open_Trans
			,(CASE
				WHEN SP.waittime = 0 THEN ''''
				WHEN SP.spid >= 51 AND LEN ((SP.waittime/1000)/3600) > 2 THEN ''99:99:99+''
				WHEN SP.spid >= 51 THEN RIGHT (''00''+CONVERT (VARCHAR (2), (SP.waittime/1000)/3600),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((SP.waittime/1000)%3600)/60),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((SP.waittime/1000)%3600)%60),2)
				ELSE ''''
				END) AS Wait_Time
			,RTRIM ((CASE
					WHEN SP.status NOT IN (''dormant'',''sleeping'') THEN UPPER (SP.status)
					ELSE LOWER (SP.status)
					END)) AS [Status]
			,RTRIM ((CASE
					WHEN SP.cmd = ''awaiting command'' THEN LOWER (SP.cmd)
					ELSE UPPER (SP.cmd)
					END)) AS Command
			,ISNULL ((SELECT [text] FROM master.sys.dm_exec_sql_text (SP.sql_handle)),'''') AS SQL_Statement
			,(CASE
				WHEN LEN (DATEDIFF (SECOND, SP.login_time, GETDATE ())/3600) > 2 THEN ''99:99:99+''
				ELSE RIGHT (''00''+CONVERT (VARCHAR (2), DATEDIFF (SECOND, SP.login_time, GETDATE ())/3600),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), (DATEDIFF (SECOND, SP.login_time, GETDATE ())%3600)/60),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), (DATEDIFF (SECOND, SP.login_time, GETDATE ())%3600)%60),2)
				END) AS Since_SPID_Login
			,(CASE
				WHEN LEN (DATEDIFF (SECOND, SP.last_batch, GETDATE ())/3600) > 2 THEN ''99:99:99+''
				ELSE RIGHT (''00''+CONVERT (VARCHAR (2), DATEDIFF (SECOND, SP.last_batch, GETDATE ())/3600),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), (DATEDIFF (SECOND, SP.last_batch, GETDATE ())%3600)/60),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), (DATEDIFF (SECOND, SP.last_batch, GETDATE ())%3600)%60),2)
				END) AS Since_Last_Batch
			,RTRIM (SP.hostname) AS Workstation_Name
			,LOWER (DB_NAME (SP.dbid)) AS Database_Name
			,CONVERT (NVARCHAR (128), RTRIM (REPLACE (REPLACE (SP.[program_name],''Microsoft® Windows® Operating System'',''Windows OS''),''Microsoft'',''MS''))) AS Application_Description
			,CONVERT (VARCHAR (6), SP.spid)+''.''+CONVERT (VARCHAR (6), SP.ecid)+(CASE WHEN SP.spid = @@SPID THEN '' •••'' ELSE '''' END) AS SPECID
		FROM [master].[sys].[sysprocesses] SP
			LEFT JOIN
				(
				  SELECT A.spid
					,ISNULL (NULLIF (A.nt_username,''''),A.loginame) AS Login_ID_Blocking
					,ROW_NUMBER () OVER
								(
								PARTITION BY A.spid
								ORDER BY (CASE WHEN ISNULL (NULLIF (A.nt_username,''''),A.loginame) = '''' THEN 2 ELSE 1 END)
									,A.ecid
								) AS sort_id
				  FROM [master].[sys].[sysprocesses] A
				) B ON B.spid = SP.blocked AND B.sort_id = 1
			LEFT JOIN
				(
				  SELECT DISTINCT X.blocked
				  FROM [master].[sys].[sysprocesses] X
				) Y ON Y.blocked = SP.spid
			LEFT JOIN [master].[sys].[dm_exec_requests] DMER ON DMER.session_id = SP.spid
		WHERE 1 = 1
	'

print '2 vSQL_String===='
print @vSQL_String
print LEN(@vSQL_String)

IF @vFilter_Active = 1
BEGIN
	SET @vSQL_String = @vSQL_String+
		'
			AND (CASE WHEN SP.open_tran <> 0 THEN '''' ELSE SP.status END) NOT IN (''dormant'',''sleeping'')
		'
END

print '3 vSQL_String===='
print @vSQL_String
print LEN(@vSQL_String)

IF @vFilter_Blocked = 1
BEGIN
	SET @vSQL_String = @vSQL_String+
		'
			AND SP.blocked <> 0
		'
END

print '4 vSQL_String===='
print @vSQL_String
print LEN(@vSQL_String)

IF @vFilter_System = 1
BEGIN
	SET @vSQL_String = @vSQL_String+
		'
			AND SP.spid >= 51
		'
END

print '5 vSQL_String===='
print @vSQL_String
print LEN(@vSQL_String)

IF @vFilter_SPID IS NOT NULL
BEGIN
	SET @vSQL_String = @vSQL_String+
		'
			AND SP.spid = '+CONVERT (VARCHAR (10), @vFilter_SPID)+'
		'
END

print '6 vSQL_String===='
print @vSQL_String
print LEN(@vSQL_String)

IF @vFilter_NT_Username_Or_Loginame IS NOT NULL
BEGIN
	SET @vSQL_String = @vSQL_String+
		'
			AND CONVERT (NVARCHAR (128), ISNULL (NULLIF (SP.nt_username,''''),SP.loginame)) = '''+@vFilter_NT_Username_Or_Loginame+'''
		'
END

print '7 vSQL_String===='
print @vSQL_String
print LEN(@vSQL_String)

IF @vFilter_SQL_Statement IS NOT NULL
BEGIN
	SET @vSQL_String = @vSQL_String+
		'
			AND (SELECT [text] FROM master.sys.fn_get_sql (SP.sql_handle)) LIKE ''%''+REPLACE (REPLACE (REPLACE ('''+@vFilter_SQL_Statement+''',''['',''[[]''),''%'',''[%]''),''_'',''[_]'')+''%''
		'
END

print '8 vSQL_String===='
print @vSQL_String
print LEN(@vSQL_String)

SET @vSQL_String = @vSQL_String+
	'
		ORDER BY
			 (CASE
				WHEN SP.blocked = 0 AND Y.blocked IS NULL THEN 999
				WHEN SP.blocked = SP.spid THEN 30
				WHEN SP.blocked = 0 AND Y.blocked IS NOT NULL THEN 20
				ELSE 10
				END)
			,SP.spid
			,SP.ecid
	'

print '9 vSQL_String===='
print @vSQL_String
print LEN(@vSQL_String)

EXEC (@vSQL_String)


skip_query:
GO