Sql查询死锁的方法

Create procedure sp_check_deadlock            
as
set nocount on
 /*
select
spid    被锁进程ID,
blocked 锁进程ID,
status  被锁状态,
SUBSTRING(SUSER_SNAME(sid),1,30) 被锁进程登陆帐号,
SUBSTRING(hostname,1,12)         被锁进程用户机器名称,
SUBSTRING(DB_NAME(dbid),1,10)    被锁进程数据名称,
cmd 被锁进程命令,
waittype 被锁进程等待类型
FROM master..sysprocesses
WHERE blocked>0

--dbcc inputbuffer(66) 输出相关锁进程的语句
*/

--创建锁进程临时表
CREATE TABLE #templocktracestatus (
   EventType  varchar(100),
   Parameters INT,
   EventInfo  varchar(200)
   )
--创建被锁进程临时表
CREATE TABLE #tempbelocktracestatus (
   EventType  varchar(100),
   Parameters INT,
   EventInfo  varchar(200)
   )
--创建之间的关联表
CREATE TABLE #locktracestatus (
   belockspid INT,
   belockspidremark varchar(20),
   belockEventType  varchar(100),
   belockEventInfo  varchar(200),
   lockspid INT,
   lockspidremark   varchar(20),
   lockEventType    varchar(100),
   lockEventInfo    varchar(200)
   )

--获取死锁进程
DECLARE dbcc_inputbuffer CURSOR READ_ONLY
FOR select spid 被锁进程ID,blocked 锁进程ID
      FROM master..sysprocesses
     WHERE blocked>0
DECLARE @lockedspid int              
DECLARE @belockedspid int

OPEN dbcc_inputbuffer 

FETCH NEXT FROM dbcc_inputbuffer INTO @belockedspid,@lockedspid
WHILE (@@fetch_status <> -1)
BEGIN
        IF (@@fetch_status <> -2)
        BEGIN
         --print '被堵塞进程'
         --select @belockedspid
         --dbcc inputbuffer(@belockedspid)
         --print '堵塞进程'
         --select @lockedspid
         --dbcc inputbuffer(@lockedspid)
         INSERT INTO #tempbelocktracestatus
           EXEC('DBCC INPUTBUFFER('+@belockedspid+')')
         INSERT INTO #templocktracestatus
           EXEC('DBCC INPUTBUFFER('+@lockedspid+')') 
         INSERT INTO #locktracestatus
           select @belockedspid,'被锁进程',a.EventType,a.EventInfo,@lockedspid,'锁进程',b.EventType,b.EventInfo
             from #tempbelocktracestatus a,#templocktracestatus b
        END
        FETCH NEXT FROM dbcc_inputbuffer INTO @belockedspid,@lockedspid
END
CLOSE dbcc_inputbuffer
DEALLOCATE dbcc_inputbuffer
select * from #locktracestatus
return (0) -- sp_check_deadlock