/*-------------------------------------------------------------------
[SCRIPT] dbo.sp_tran_locks 
[DATABASE] master (system object)
[DESCRIPTION] sys.dm_db_tran_locks decoded
[MAJ PAR] DATAFLY - Arian Papillon
[DATEMAJ] 20170122 
[EXAMPLE] EXEC dbo.sp_tran_locks 
    @spid = NULL, -- int
    @type = NULL, -- nvarchar(60)
    @mode = NULL, -- nvarchar(60)
    @status = NULL, -- nvarchar(60)
    @Database = NULL, -- sysname
    @BlockedOnly = 0 -- bit
-------------------------------------------------------------------*/
USE master;
GO

CREATE PROCEDURE dbo.sp_tran_locks
    @spid INT = NULL
  , @type NVARCHAR(60) = NULL
  , @mode NVARCHAR(60) = NULL
  , @status NVARCHAR(60) = NULL
  , @Database sysname = NULL
  , @BlockedOnly BIT = 0
AS
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SET NOCOUNT ON;
    DECLARE @locktable TABLE
        (
          resource_type NVARCHAR(60)
        , request_session_id INT
        , DatabaseName sysname NULL
        , SchemaName sysname NULL
        , ObjectName sysname NULL
        , IndexName sysname NULL
        , IndexType NVARCHAR(60)
        , PartitionNumber NVARCHAR(16)
        , request_mode NVARCHAR(60)
        , request_status NVARCHAR(60)
        , blocking_session_id INT
        , host_name NVARCHAR(128)
        , program_name NVARCHAR(128)
        , login_name NVARCHAR(128)
        , LastRequestingText NVARCHAR(MAX)
        , Localize NVARCHAR(MAX)
        , resource_database_id INT
        , resource_description NVARCHAR(256)
        , resource_associated_entity_id BIGINT
        );
    INSERT  @locktable
            ( resource_type
            , request_session_id
            , DatabaseName
            , SchemaName
            , ObjectName
            , IndexName
            , IndexType
            , PartitionNumber
            , request_mode
            , request_status
            , blocking_session_id
            , [host_name]
            , [program_name]
            , login_name
            , LastRequestingText
            , Localize
            , resource_database_id
            , resource_description
            , resource_associated_entity_id
            )
            SELECT  resource_type
                  , request_session_id
                  , DB_NAME(resource_database_id)
                  , '-' SchemaName
                  , '-' ObjectName
                  , '-' IndexName
                  , '-' IndexType
                  , '-' PartitionNumber
                  , request_mode
                  , request_status
                  , dm_os_waiting_tasks.blocking_session_id
                  , host_name
                  , program_name
                  , login_name
                  , dm_exec_sql_text.text AS LastRequestingText
                  , ''
                  , resource_database_id
                  , dm_tran_locks.resource_description
                  , resource_associated_entity_id
            FROM    sys.dm_tran_locks
                    JOIN sys.dm_exec_sessions ON dm_tran_locks.request_session_id = dm_exec_sessions.session_id
                    JOIN sys.dm_exec_connections ON dm_exec_connections.session_id = dm_tran_locks.request_session_id
                    LEFT JOIN sys.dm_os_waiting_tasks ON dm_tran_locks.lock_owner_address = dm_os_waiting_tasks.resource_address
                    CROSS APPLY sys.dm_exec_sql_text(dm_exec_connections.most_recent_sql_handle)
            WHERE   ( resource_type = @type
                      OR @type IS NULL
                    )
                    AND ( request_session_id = @spid
                          OR @spid IS NULL
                        )
                    AND ( request_mode = @mode
                          OR @mode IS NULL
                        )
                    AND ( request_status = @status
                          OR @status IS NULL
                        )
                    AND ( DB_NAME(resource_database_id) = @Database
                          OR @Database IS NULL
                        )
                    AND ( dm_os_waiting_tasks.blocking_session_id IS NOT NULL
                          OR @BlockedOnly = 0
                        ); 

    DECLARE @resource_type NVARCHAR(60)
      , @resource_database_id INT
      , @resource_description NVARCHAR(256)
      , @resource_associated_entity_id BIGINT;
    DECLARE @fileid VARCHAR(4)
      , @pageid VARCHAR(10)
      , @slotid VARCHAR(10);
    DECLARE @objectid INT
      , @indexid INT
      , @partitionnumber INT;
    DECLARE @objectname sysname
      , @indexname sysname
      , @schemaname sysname
      , @indextype NVARCHAR(60);
    DECLARE @sqlcmd NVARCHAR(4000)
      , @localize NVARCHAR(MAX);
    DECLARE lockdecode CURSOR
    FOR
        SELECT  resource_type
              , resource_database_id
              , resource_description
              , resource_associated_entity_id
        FROM    @locktable;
    OPEN lockdecode;
    FETCH NEXT FROM lockdecode INTO @resource_type, @resource_database_id,
        @resource_description, @resource_associated_entity_id;

    WHILE ( @@FETCH_STATUS = 0 )
        BEGIN
            IF @resource_type IN ( 'KEY', 'PAGE', 'RID' )
                BEGIN
                    SET @sqlcmd = 'SELECT @schemaname=s.name, @objectname=t.name'
                        + ', @indexname=i.name, @indextype=i.type_desc, @partitionnumber=p.partition_number '
                        + 'FROM [' + DB_NAME(@resource_database_id)
                        + '].sys.partitions p ' + 'JOIN ['
                        + DB_NAME(@resource_database_id)
                        + '].sys.indexes i ON p.object_id = i.object_id and p.index_id = i.index_id '
                        + 'JOIN [' + DB_NAME(@resource_database_id)
                        + '].sys.objects t ON i.object_id = t.object_id '
                        + 'JOIN [' + DB_NAME(@resource_database_id)
                        + '].sys.schemas s ON t.schema_id = s.schema_id '
                        + 'WHERE partition_id = '
                        + CAST(@resource_associated_entity_id AS VARCHAR(32));
                    EXEC sp_executesql @sqlcmd,
                        N'@schemaname sysname OUT, @objectname sysname OUT, @indexname sysname OUT, @indextype nvarchar(60) OUT, @partitionnumber INT OUT',
                        @schemaname OUT, @objectname OUT, @indexname OUT,
                        @indextype OUT, @partitionnumber OUT;
                END;
            IF @resource_type = 'OBJECT'
                BEGIN
                    SET @sqlcmd = 'SELECT @schemaname=s.name, @objectname=t.name '
                        + 'FROM [' + DB_NAME(@resource_database_id)
                        + '].sys.objects t ' + 'JOIN ['
                        + DB_NAME(@resource_database_id)
                        + '].sys.schemas s ON t.schema_id = s.schema_id '
                        + 'WHERE t.object_id = '
                        + CAST(@resource_associated_entity_id AS VARCHAR(32));
                    EXEC sp_executesql @sqlcmd,
                        N'@schemaname sysname OUT, @objectname sysname OUT',
                        @schemaname OUT, @objectname OUT;
                    UPDATE  @locktable
                    SET     SchemaName = @schemaname
                          , ObjectName = @objectname
                    WHERE CURRENT OF lockdecode;
                END;
            ELSE
                IF @resource_type = 'PAGE'
                    BEGIN
                        SET @fileid = PARSENAME(RTRIM(REPLACE(@resource_description,
                                                              ':', '.')), 2); 
                        SET @pageid = PARSENAME(RTRIM(REPLACE(@resource_description,
                                                              ':', '.')), 1);
                        SET @localize = 'SELECT sys.fn_physlocFormatter(%%physloc%%) PhysicalLocation,%%lockres%% AS LockResHash,'
                            + @objectname + '.* FROM ['
                            + DB_NAME(@resource_database_id) + '].['
                            + @schemaname + '].[' + @objectname
                            + '] WITH (READUNCOMMITTED, INDEX = '
                            + ISNULL(@indexname, '0')
                            + ') CROSS APPLY sys.fn_physlocCracker(%%physloc%%) PL WHERE PL.file_id='
                            + @fileid + ' AND PL.page_id=' + @pageid;
                        UPDATE  @locktable
                        SET     SchemaName = @schemaname
                              , ObjectName = @objectname
                              , IndexName = ISNULL(@indexname, '-')
                              , Localize = RTRIM(@localize)
                              , IndexType = @indextype
                              , PartitionNumber = CAST(@partitionnumber AS NVARCHAR(16))
                        WHERE CURRENT OF lockdecode;
                    END;
                ELSE
                    IF @resource_type = 'RID'
                        BEGIN
                            SET @fileid = PARSENAME(RTRIM(REPLACE(@resource_description,
                                                              ':', '.')), 3); 
                            SET @pageid = PARSENAME(RTRIM(REPLACE(@resource_description,
                                                              ':', '.')), 2); 
                            SET @slotid = PARSENAME(RTRIM(REPLACE(@resource_description,
                                                              ':', '.')), 1);							  

                            SET @localize = 'SELECT sys.fn_physlocFormatter(%%physloc%%) PhysicalLocation,%%lockres%% AS LockResHash,'
                                + @objectname + '.* FROM ['
                                + DB_NAME(@resource_database_id) + '].['
                                + @schemaname + '].[' + @objectname
                                + '] WITH (READUNCOMMITTED, INDEX = 0) CROSS APPLY sys.fn_physlocCracker(%%physloc%%) PL WHERE PL.file_id='
                                + @fileid + ' AND PL.page_id=' + @pageid
                                + ' AND slot_id=' + @slotid;
                            UPDATE  @locktable
                            SET     SchemaName = @schemaname
                                  , ObjectName = @objectname
                                  , Localize = RTRIM(@localize)
                                  , IndexType = @indextype
                                  , PartitionNumber = CAST(@partitionnumber AS NVARCHAR(16))
                            WHERE CURRENT OF lockdecode;
                        END;
                    ELSE
                        IF @resource_type = 'KEY'
                            BEGIN
                                SET @localize = 'SELECT %%lockres%% AS LockResHash,* FROM ['
                                    + DB_NAME(@resource_database_id) + '].['
                                    + @schemaname + '].[' + @objectname
                                    + '] WITH (READUNCOMMITTED, INDEX = '
                                    + @indexname + ') WHERE %%lockres%% = '''
                                    + RTRIM(@resource_description) + '''';
                                UPDATE  @locktable
                                SET     SchemaName = @schemaname
                                      , ObjectName = @objectname
                                      , IndexName = @indexname
                                      , IndexType = @indextype
                                      , Localize = RTRIM(@localize)
                                      , PartitionNumber = CAST(@partitionnumber AS NVARCHAR(16))
                                WHERE CURRENT OF lockdecode;
                            END;
            FETCH NEXT FROM lockdecode INTO @resource_type,
                @resource_database_id, @resource_description,
                @resource_associated_entity_id;
        END;
    CLOSE lockdecode;
    DEALLOCATE lockdecode;
    SELECT  resource_type
          , request_session_id
          , DatabaseName
          , SchemaName
          , ObjectName
          , IndexName
          , IndexType
          , PartitionNumber
          , request_mode
          , request_status
          , ISNULL(CAST(blocking_session_id AS VARCHAR(5)), '-') blocking_session_id
          , [host_name]
          , [program_name]
          , login_name
          , resource_database_id
          , resource_description
          , resource_associated_entity_id
          , LastRequestingText
          , Localize
    FROM    @locktable;
GO

-- mark as system object
EXEC sp_MS_marksystemobject 'sp_tran_locks';
GO

