Blocking Tree

SET NOCOUNT ON
GO
DROP TABLE IF EXISTS #T
GO
DROP TABLE IF EXISTS #buffer_results
GO
CREATE TABLE #buffer_results (EventType VARCHAR(30),Parameters INT,EventInfo NVARCHAR(4000), SPID INT );
CREATE TABLE #t (ID INT PRIMARY KEY IDENTITY, SPID INT, BLOCKED INT, BATCH NVARCHAR(MAX), SP NVARCHAR(4000))
DECLARE @i INT, @sql NVARCHAR(MAX), @SPID INT
INSERT INTO #t (SPID, BLOCKED, BATCH)
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
SET @i = @@ROWCOUNT
WHILE @i > 0
       BEGIN
              BEGIN TRY;
                     SELECT @SPID = SPID FROM #t WHERE ID = @i
                     INSERT INTO #buffer_results
                     (
                           EventType,
                           Parameters,
                           EventInfo
                     )
                     EXEC sp_executesql
                           N'DBCC INPUTBUFFER(@session_id) WITH NO_INFOMSGS;',
                           N'@session_id SMALLINT',
                           @SPID;
                     UPDATE #buffer_results SET SPID = @SPID WHERE SPID IS NULL
              END TRY
              BEGIN CATCH
              END CATCH;
              SET @i = @i - 1
       END
UPDATE a
SET SP = ISNULL(REPLACE(b.EventInfo,';1',''),'')
FROM #t a
JOIN #buffer_results b ON a.SPID = b.SPID
;WITH BLOCKERS (SPID, SP, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
SP,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.SP,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N'    ' + REPLICATE (N'|         ', LEN (LEVEL)/4 - 1) +
CASE WHEN (LEN(LEVEL)/4 - 1) = 0
THEN 'HEAD -  '
ELSE '|------  ' END
+ CAST (SPID AS NVARCHAR (10)) + ' - ' +SP + N' ' + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE IF EXISTS #T
GO
DROP TABLE IF EXISTS #buffer_results
GO