SELECT T1.REQUEST_SESSION_ID AS [WAITER SPID], -- SPID OF WAITER
T2.BLOCKING_SESSION_ID [BLOCKER SPID],
DB_NAME(RESOURCE_DATABASE_ID) AS [DATABASE],
T1.RESOURCE_TYPE AS [RESOURCE TYPE],
CASE T1.RESOURCE_TYPE WHEN 'OBJECT' THEN OBJECT_NAME(T1.RESOURCE_ASSOCIATED_ENTITY_ID)
ELSE T1.RESOURCE_ASSOCIATED_ENTITY_ID END AS [BLOCKING OBJECT],
T1.REQUEST_MODE AS [LOCK REQ MODE],
T2.WAIT_DURATION_MS AS [WAIT TIME],
R.TEXT AS WAITER_BATCH,
SUBSTRING(R.TEXT,EXR.STATEMENT_START_OFFSET/2,
(CASE WHEN EXR.STATEMENT_END_OFFSET = -1
THEN LEN(CONVERT(NVARCHAR(MAX), R.TEXT)) * 2
ELSE EXR.STATEMENT_END_OFFSET END - EXR.STATEMENT_START_OFFSET)/2) AS WAITER_STMT,
P.TEXT AS BLOCKER_BATCH,
SUBSTRING(P.TEXT,BLK_EXR.STATEMENT_START_OFFSET/2,
(CASE WHEN BLK_EXR.STATEMENT_END_OFFSET = -1
THEN LEN(CONVERT(NVARCHAR(MAX), P.TEXT)) * 2
ELSE BLK_EXR.STATEMENT_END_OFFSET END - BLK_EXR.STATEMENT_START_OFFSET)/2) AS BLOCKER_STMT
FROM
SYS.DM_TRAN_LOCKS AS T1,
SYS.DM_OS_WAITING_TASKS AS T2,
SYS.DM_EXEC_REQUESTS EXR
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(EXR.SQL_HANDLE) AS R,
SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) AS P,
SYS.DM_EXEC_REQUESTS BLK_EXR
WHERE
T1.LOCK_OWNER_ADDRESS = T2.RESOURCE_ADDRESS
AND EXR.SESSION_ID = T1.REQUEST_SESSION_ID
AND SP.SPID = T2.BLOCKING_SESSION_ID
AND BLK_EXR.SESSION_ID = T2.BLOCKING_SESSION_ID