SP_WHAT - A New attempt to find the Blocks

Hi buddies,

The following procedure can be used to find whether there are any blocks in the sql server.
The procedure if run in Master database , can be used like any other
system stored procedure from any database.
The output of the proc will contain the following

SPID OF RUNNING APPLICATION
APPLICATION NAME
EXECUTING DATA
BLOCKED (Y/N)
BLOCKING SPID
BLOCKING APPLICATION
EXECUTING DATA (BLOCKING APPLICATION)

The Procedure's target version is SQL SERVER 2000.Stay tuned for more details and enhancements.



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
/*
--DEBUGG

SELECT * FROM #T2 A
SELECT * FROM #T3 B
--DEBUGG
*/
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