finding the blocks in sql server

CREATE PROC SP_WHAT
AS
BEGIN
/*CODE WRITTEN BY
PAARTHASARATHY K
http://InayathilEnthanGeetham.blogspot.com/
http://PaarthasarathyK.blogspot.com/
*//*TO FIND THE SPID'S THAT ARE ACTIVE AND WHAT THEY ARE EXECUTING*/CREATE TABLE #T1
(
A SYSNAME,
B SYSNAME,
[INPUT_BUFFER] NVARCHAR(1200),[SPID] INT NULL
);
DECLARE @A NVARCHAR(400),@B INT

DECLARE SP_IDS CURSOR
FOR SELECT SPID FROM SYSPROCESSES
WHERE STATUS ='RUNNABLE'
--STATUS <> 'SLEEPING'AND
--STATUS <> 'BACKGROUND'
AND SPID <> @@SPID
AND PROGRAM_NAME <> 'SQL PROFILER'
UNION ALL
SELECT SPID FROM SYSPROCESSES
WHERE BLOCKED <>0


OPEN SP_IDS
FETCH NEXT FROM SP_IDS INTO @B

WHILE @@FETCH_STATUS =0
BEGIN

SET @A = 'DBCC INPUTBUFFER('+RTRIM(@B)+')WITH NO_INFOMSGS;';


INSERT #T1(A,B,[INPUT_BUFFER]) EXEC SP_EXECUTESQL @A

UPDATE #T1 SET [SPID] = @B WHERE [SPID] IS NULL
FETCH NEXT FROM SP_IDS INTO @B
END


CLOSE SP_IDS
DEALLOCATE SP_IDS

CREATE TABLE #T2
(
SPID INT,
PROGRAM_NAME SYSNAME,
INPUT_BUFFER NVARCHAR(1200),
BLOCKED VARCHAR(2),
BLOCKING_PROCESS VARCHAR(10),
PROGRAM_NAME1 SYSNAME,
INPUT_BUFFER1 NVARCHAR(1200)
)


INSERT INTO #T2
SELECT A.SPID,B.PROGRAM_NAME, A.INPUT_BUFFER ,
CASE B.BLOCKED WHEN 0 THEN 'N' ELSE 'Y' END ,
CASE WHEN B.BLOCKED <>0 THEN CONVERT(VARCHAR(10),B.BLOCKED) ELSE 'NO BLOCK' END ,
'',''
FROM #T1 A,
MASTER..SYSPROCESSES B
WHERE A.SPID = B.SPID


UPDATE A
SET A.PROGRAM_NAME1 = B.PROGRAM_NAME
FROM #T2 A ,
SYSPROCESSES B
WHERE A.BLOCKING_PROCESS = B.SPID
AND A.BLOCKED = 'Y'



CREATE TABLE #T3
(
A SYSNAME,
B SYSNAME,
[INPUT_BUFFER] NVARCHAR(1200),[SPID] INT NULL
);

DECLARE CURSOR_2 CURSOR
FOR SELECT BLOCKING_PROCESS FROM #T2
WHERE BLOCKED ='Y'


OPEN CURSOR_2
FETCH NEXT FROM CURSOR_2 INTO @B

WHILE @@FETCH_STATUS =0
BEGIN

SET @A = 'DBCC INPUTBUFFER('+RTRIM(@B)+')WITH NO_INFOMSGS;';


INSERT #T3(A,B,[INPUT_BUFFER]) EXEC SP_EXECUTESQL @A

UPDATE #T3 SET [SPID] = @B WHERE [SPID] IS NULL
FETCH NEXT FROM CURSOR_2 INTO @B
END

CLOSE CURSOR_2
DEALLOCATE CURSOR_2
/*
The Procedure's target version is SQL SERVER 2000.
If u have something to say on this, pls leave ur comments either in my blogspot or mail me.
*/UPDATE A
SET A.INPUT_BUFFER1 = B.INPUT_BUFFER
FROM #T2 A, #T3 B
WHERE A.BLOCKING_PROCESS <> 'NO BLOCK' AND
A.BLOCKING_PROCESS = CONVERT (VARCHAR(10),B.SPID)

SELECT DISTINCT LTRIM(RTRIM(SPID)) 'SPID'
,LTRIM(RTRIM(PROGRAM_NAME)) 'APPLICATION NAME',
LTRIM(RTRIM(INPUT_BUFFER)) 'EXECUTING DATA',
LTRIM(RTRIM(BLOCKED)) 'BLOCKED (Y/N)',
LTRIM(RTRIM(BLOCKING_PROCESS)) 'BLOCKING SPID' ,
LTRIM(RTRIM(PROGRAM_NAME1)) 'BLOCKING APPLICATION',
LTRIM(RTRIM(INPUT_BUFFER1)) 'EXECUTING DATA (BLOCKING APPLICATION)' FROM #T2


DROP TABLE #T1
DROP TABLE #T2
DROP TABLE #T3

END