1 SELECT
 2   l.request_session_id AS sid,
 3   r.status,
 4   d.name AS [database],
 5   o.name AS object,
 6   l.request_mode,
 7   l.request_status,
 8   r.blocking_session_id AS blocking_sid,
 9   r.wait_type,
10   r.wait_time,
11   r.total_elapsed_time,
12   r.percent_complete,
13   p.cpu,
14   p.physical_io,
15   p.memusage,
16   p.loginame,
17   p.hostname,
18   p.program_name,
19   t.text
20 FROM sys.dm_tran_locks AS l
21 INNER JOIN sys.databases AS d ON d.database_id = l.resource_database_id
22 INNER JOIN sys.objects AS o ON o.object_id = l.resource_associated_entity_id
23 LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = l.request_session_id
24 LEFT JOIN sys.sysprocesses AS p ON p.spid = l.request_session_id
25 CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
26 WHERE
27   d.database_id = DB_ID() AND
28   l.resource_type = 'OBJECT' AND
29   r.session_id != @@SPID
30 ORDER BY
31   d.name,
32   l.request_session_id,
33   o.name