IF EXISTS (SELECT * FROM sysobjects 
           WHERE id = OBJECT_ID('dbo.find_blocker') 
           AND sysstat & 0xf = 4)
	DROP PROCEDURE dbo.find_blocker

GO

CREATE PROCEDURE find_blocker
AS
/* Query to find spids at head of a blocking chain, their input buffers, 
   and the type of blocking locks they hold */
DECLARE @blocker_spid smallint, 
        @spid SMALLINT, 
        @i_buff_string char(30),
        @CMD VARCHAR(255) 
SET NOCOUNT ON
/* Get all blocked spids */
SELECT DISTINCT spid,
                suid,
                dbid,  
                blocked 
INTO #blk 
FROM master..sysprocesses (NOLOCK) 
WHERE blocked <> 0
OR (spid IN (SELECT blocked FROM master..sysprocesses where blocked <> 0)
    AND blocked = 0) 

/* list all blocked spids */
SELECT "Blocked Processes"
SELECT spid as "BLOCKED SPID", 
       SUSER_NAME(suid) as "LOGIN NAME", 
       DB_NAME(dbid) as "DB NAME", 
       blocked as "BLOCKING SPID"
       FROM #blk
       WHERE blocked <> 0 

/* dump all input buffers for spids that are blocked */
SELECT "Last Command from Blocked Processes"
SELECT @spid = (SELECT MIN(spid) FROM #blk WHERE blocked <> 0)
WHILE @spid IS NOT NULL
  BEGIN
    SELECT @CMD = 'SELECT "BLOCKED SPID ' + CONVERT(VARCHAR(3),@spid) + '"' +
              " DBCC INPUTBUFFER(" + CONVERT(VARCHAR(3),@spid) + ") " 
    EXEC (@CMD)
    SELECT @spid = (SELECT MIN(spid) FROM #blk
                    WHERE spid > @spid
                    AND blocked <> 0)
  END

/* For each spid at the head of a blocking chain 
   print the type of blocking locks it holds */
/* dump all input buffers for spids that are blocked */
SELECT "Locks held by involved processes"
SELECT spid, 
       l.type, 
       locktype = name, 
       table_id = id, 
       page, 
       dbid 
FROM master..syslocks l, master.dbo.spt_values v 
WHERE l.type = v.number 
AND v.type='L' 
AND (l.type & 256)=256 
AND spid IN (SELECT blocked FROM #blk) 
ORDER BY spid 

/* delete all blocking spids except blockers */
DELETE FROM #blk 
WHERE blocked <> 0 

/* get each spid from sysprocesses which is referenced in 
   the "blocked" column of #blk. This should be the head 
   of each blocking chain */
SELECT "Blocking Process(es)"
SELECT "BLOCKED SPID"=spid, 
       "LOGIN NAME"=SUSER_NAME(suid), 
       "DB NAME"=DB_NAME(dbid), 
       "BLOCKING SPID"=blocked FROM #blk
       WHERE blocked = 0 
/* For each spid at the head of a blocking chain 
   print its input buffer to show what query it's running */
SELECT "  Last Command from Blocking Processes"
SELECT @spid = (SELECT MIN(spid) FROM #blk)
WHILE @spid IS NOT NULL
  BEGIN
    SELECT @CMD = 'SELECT "BLOCKED SPID ' + CONVERT(VARCHAR(3),@spid) + '"' +
              " DBCC INPUTBUFFER(" + CONVERT(VARCHAR(3),@spid) + ") " 
    EXEC (@CMD)
    SELECT @spid = (SELECT MIN(spid) FROM #blk
                    WHERE spid > @spid)
  END
DROP table #blk
GO