tran_locks.sql

sqlserver_locks.sql

-- Based on https://gist.github.com/aleksp99/2ca3401be8965f5ea72187a7baad739d

SELECT
	dm_tran_locks.request_session_id AS IDSessions,
	CASE
		WHEN resource_type = 'object'
			THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
		ELSE OBJECT_NAME(partitions.object_id)
	END AS ObjectName,
	indexes.name AS IndexName,
	dm_tran_locks.resource_type,
	RTRIM(dm_tran_locks.resource_description) AS Description,

	CASE dm_tran_locks.request_mode
		WHEN 'S'
			THEN 'S (SELECT)'
		WHEN 'X'
			THEN 'X (INSERT)'
		WHEN 'U (UPDATE)'
			THEN 'Table'
		ELSE dm_tran_locks.request_mode
	END AS RequestMode,
	CASE dm_tran_locks.request_status
		WHEN 'GRANT'
			THEN 'Granted'
		ELSE 'Waiting'
	END AS LockStatus,
	CASE ISNULL(indexes.index_id, 0)
		WHEN 1
			THEN 1
		ELSE 0
	END AS ClusteredIndex,
	partitions.rows as approx_row_count
FROM
	sys.dm_tran_locks AS dm_tran_locks
	LEFT JOIN sys.partitions AS partitions
	ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
	LEFT JOIN sys.indexes AS indexes
	ON indexes.object_id = partitions.object_id
		AND indexes.index_id = partitions.index_id
WHERE
	1=1
	AND resource_associated_entity_id > 0
	AND resource_database_id = DB_ID() -- Restrict to current DB (in particular, we don't care about tempdb)
	AND CASE
		WHEN resource_type = 'object'
			THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
		ELSE OBJECT_NAME(partitions.object_id)
	END NOT LIKE 'sys%'
ORDER BY ObjectName, IndexName, RequestMode