查询Sqlserver数据库死锁的一个存储过程

 1 use master
 2 go
 3 
 4 CREATE procedure sp_who_lock   
 5 as     
 6 begin     
 7    declare @spid int     
 8    declare @blk int     
 9    declare @count int     
10    declare @index int     
11    declare @lock tinyint      
12    set @lock=0      
13  create table #temp_who_lock      
14  (      
15   id int identity(1,1),      
16   spid int,      
17   blk int     
18  )  
19      
20  if @@error<>0 return @@error      
21  insert into #temp_who_lock(spid,blk)      
22  select 0 ,blocked       
23  from (select * from master..sysprocesses where blocked>0)a      
24  where not exists(select * from  master..sysprocesses where a.blocked =spid and blocked>0)      
25  union 
26  select spid,blocked from  master..sysprocesses where blocked>0 
27       
28  if @@error<>0 return @@error      
29  select @count=count(*),@index=1 from #temp_who_lock      
30  if @@error<>0 return @@error      
31  if @count=0      
32  begin     
33   select ‘没有阻塞和死锁信息‘     
34   return 0      
35  end     
36  while @index<=@count      
37  begin     
38   if exists(select 1 from #temp_who_lock a where id>@index and exists(select 1 from #temp_who_lock 
39   where id<=@index and a.blk=spid))      
40   begin     
41    set @lock=1      
42    select @spid=spid,@blk=blk from #temp_who_lock where id=@index     
43    select ‘引起数据库死锁的是: ‘+ CAST(@spid AS VARCHAR(10)) + ‘进程号,其执行的SQL语法如下‘     
44    select  @spid, @blk    
45    dbcc inputbuffer(@spid)      
46    dbcc inputbuffer(@blk)      
47   end     
48   set @index=@index+1      
49  end     
50  if @lock=0       
51  begin     
52   set @index=1      
53   while @index<=@count      
54   begin     
55    select @spid=spid,@blk=blk from #temp_who_lock where id=@index     
56    if @spid=0      
57     select ‘引起阻塞的是:‘+cast(@blk as varchar(10))+ ‘进程号,其执行的SQL语法如下‘     
58    else      
59     select ‘进程号SPID：‘+ CAST(@spid AS VARCHAR(10))+ ‘被‘ + ‘进程号SPID：‘+ CAST(@blk AS VARCHAR(10)) 
60            +‘阻塞,其当前进程执行的SQL语法如下‘     
61    dbcc inputbuffer(@spid)    
62    dbcc inputbuffer(@blk)      
63    set @index=@index+1      
64   end     
65  end     
66  drop table #temp_who_lock      
67  return 0      
68 end           
69  
70  
71 GO