SQL Server: table & index names from sys.dm_tran_locks

Martin Smith gave a great answer on StackOverflow - as he often does - when someone was looking to get a table name from the information in sys.dm_tran_locks.
http://stackoverflow.com/a/7821066

Here's my take at an answer - trying to cut down on the rows returned to keep the list short in busy databases.

;WITH tLock_cte(request_session_id, dbName, tableName, indexName, resource_type, request_mode) AS
(SELECT tL.request_session_id,
        db_name(tL.resource_database_id) AS dbName,
        CASE WHEN tL.resource_database_id <> db_id() THEN '[in another database]' 
             WHEN tL.resource_type = 'OBJECT' 
                  THEN object_name(tL.resource_associated_entity_id)
             WHEN tL.resource_type = 'ALLOCATION_UNIT' THEN object_name(sp3.[object_id])
             WHEN au1.type = 0 THEN 'DEFERRED DROP'
             WHEN au1.type = 2 THEN object_name(sp2.[object_id])
             ELSE object_name(sp1.[object_id])
        END       AS tableName,
        si.[name] AS indexName,
        tL.resource_type,
        tL.request_mode
 FROM sys.dm_tran_locks               AS tL
 LEFT OUTER JOIN sys.allocation_units AS au1 
 ON au1.container_id = tL.resource_associated_entity_id
 LEFT OUTER JOIN sys.allocation_units AS au2
 ON au2.allocation_unit_id = tL.resource_associated_entity_id
 LEFT OUTER JOIN sys.partitions       AS sp1
 ON sp1.hobt_id = au1.container_id
 LEFT OUTER JOIN sys.partitions       AS sp2
 ON sp2.[partition_id] = au1.container_id
 LEFT OUTER JOIN sys.partitions       AS sp3
 ON sp3.[partition_id] = au2.container_id
 LEFT OUTER JOIN sys.indexes          AS si 
 ON si.[object_id]  = COALESCE(sp1.[object_id], sp2.[object_id], sp3.[object_id])
    AND si.index_id = COALESCE(sp1.index_id, sp2.index_id, sp3.index_id)
 WHERE tL.resource_associated_entity_id IS NOT NULL
 AND   tL.resource_type IN ('OBJECT', 'PAGE', 'RID', 'KEY',
                            'EXTENT', 'ALLOCATION_UNIT', 'HOBT'))
SELECT   request_session_id, dbName, tableName, indexName, resource_type, request_mode
FROM     tLock_cte
GROUP BY request_session_id, dbName, tableName, indexName, resource_type, request_mode;

Let's give it a test.

USE LKN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
DROP TABLE IF EXISTS test_locks;
CREATE TABLE test_locks
(num BIGINT);
GO
BEGIN TRAN
CREATE INDEX nci_test ON test_locks(num);
INSERT INTO test_locks SELECT 1;
DELETE FROM test_locks WHERE 1 = 0;

--COMMIT TRAN

The results...

Good enough for now.

So let's commit the transaction in order to release the locks.  And I'll have more for you another day...

COMMIT TRAN