Post date: Feb 13, 2013 3:56:47 PM
Using sys,dm_tran_locks we can get information about which resources that are being locked.
If we want to select those rows that are locked we need to figure out which object they belong to and then find them using %%lockres%%.
Snippet: Get all rows that are being locked by KEY in the current database
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;DECLARE @sql VARCHAR(MAX) = '';IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp -- Collect information of what object the locks belong to-- http://stackoverflow.com/questions/7820907/how-to-find-out-what-table-a-page-lock-belongs-toSELECT dm_tran_locks.request_session_id, dm_tran_locks.resource_database_id, DB_NAME(dm_tran_locks.resource_database_id) AS dbname, CASE WHEN resource_type = 'object' THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id) ELSE OBJECT_NAME(partitions.OBJECT_ID) END AS ObjectName, partitions.index_id, indexes.name AS index_name, dm_tran_locks.resource_type, dm_tran_locks.resource_description, dm_tran_locks.resource_associated_entity_id, dm_tran_locks.request_mode, dm_tran_locks.request_status, cleanlockrs INTO #tmp FROM sys.dm_tran_locks LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id CROSS APPLY( SELECT LEFT(SUBSTRING(resource_description, 2, LEN(resource_description)), LEN(resource_description) - 2)) clean(cleanlockrs)WHERE resource_associated_entity_id > 0 AND resource_database_id = DB_ID() ORDER BY request_session_id, resource_associated_entity_id SELECT @sql = @sql + 'SELECT cols.*, #tmp.* FROM #tmp CROSS APPLY(SELECT '''+cleanlockrs+''',* FROM '+ObjectName+' WHERE %%lockres%% like ''%'+cleanlockrs+'%'') cols(lockRs,'+colList+') WHERE cleanlockrs = lockRs;' FROM ( SELECT DISTINCT cleanlockrs, ObjectName, resource_type FROM #tmp t ) t(cleanlockrs, objectName, resource_type)CROSS APPLY( -- Get Column Names comma separated -- http://stackoverflow.com/questions/4936634/comma-separated-list-of-all-columns-in-the-database-tablename-column-names SELECT STUFF( ( SELECT ', ' + C.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS AS C WHERE C.TABLE_NAME = t.ObjectName ORDER BY C.ORDINAL_POSITION FOR XML PATH('') ), 1, 2, '') AS Columns) cols(colList)WHERE resource_type = 'KEY'EXEC (@sql)