sp_who_lock

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