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
BEGINDROP 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 (MAX) = NULLASSET 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?'
BEGINRAISERROR
        (
'
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_queryENDIF @vFilter_Active_Blocked_System = 'O?'
BEGINRAISERROR
        ('
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_queryEND----------------------------------------------------------------------------------------------------------------------
--    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 (MAX)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
----------------------------------------------------------------------------------------------------------------------

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:59:59+''
                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:59:59+''
                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:59:59+''
                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:59:59+''
                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:59:59+''
                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:59:59+''
                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'IF @vFilter_Active = 1
BEGINSET @vSQL_String = @vSQL_String+'
            AND (CASE
                    WHEN SP.open_tran <> 0 THEN ''''
                    ELSE SP.status
                    END) NOT IN (''dormant'',''sleeping'')
        'ENDIF @vFilter_Blocked = 1
BEGINSET @vSQL_String = @vSQL_String+'
            AND SP.blocked <> 0
        'ENDIF @vFilter_System = 1
BEGINSET @vSQL_String = @vSQL_String+'
            AND SP.spid >= 51
        'ENDIF @vFilter_SPID IS NOT NULL
BEGINSET @vSQL_String = @vSQL_String+'
            AND SP.spid = '+CONVERT (VARCHAR (10), @vFilter_SPID)+'
        'ENDIF @vFilter_NT_Username_Or_Loginame IS NOT NULL
BEGINSET @vSQL_String = @vSQL_String+'
            AND CONVERT (NVARCHAR (128), ISNULL (NULLIF (SP.nt_username,''''),SP.loginame)) = '''+@vFilter_NT_Username_Or_Loginame+'''
        'ENDIF @vFilter_SQL_Statement IS NOT NULL
BEGINSET @vSQL_String = @vSQL_String+'
            AND (SELECT [text] FROM master.sys.fn_get_sql (SP.sql_handle)) LIKE ''%''+REPLACE (REPLACE (REPLACE ('''+@vFilter_SQL_Statement+''',''['',''[[]''),''%'',''[%]''),''_'',''[_]'')+''%''
        'ENDSET @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
    'EXEC (@vSQL_String)
skip_query:GO