SQL大圣之路笔记——SQL 通过创建存储过程，查看当前进程情况（是否造成表锁死等）

存储过程：

  1 create procedure sp_who_lock
  2 
  3 as
  4 
  5 begin
  6 
  7 declare @spid int,@bl int,
  8 
  9 @intTransactionCountOnEntry int,
 10 
 11          @intRowcount int,
 12 
 13          @intCountProperties int,
 14 
 15          @intCounter int
 16 
 17 
 18 
 19 create table #tmp_lock_who (
 20 
 21 id int identity(1,1),
 22 
 23 spid smallint,
 24 
 25 bl smallint)
 26 
 27 
 28 
 29 IF @@ERROR<>0 RETURN @@ERROR
 30 
 31 
 32 
 33 insert into #tmp_lock_who(spid,bl) select   0 ,blocked
 34 
 35    from (select * from sysprocesses where   blocked>0 ) a
 36 
 37    where not exists(select * from (select * from sysprocesses where   blocked>0 ) b
 38 
 39    where a.blocked=spid)
 40 
 41    union select spid,blocked from sysprocesses where   blocked>0
 42 
 43 
 44 
 45 IF @@ERROR<>0 RETURN @@ERROR
 46 
 47 
 48 
 49 -- 找到临时表的记录数
 50 
 51 select @intCountProperties = Count(*),@intCounter = 1
 52 
 53 from #tmp_lock_who
 54 
 55 
 56 
 57 IF @@ERROR<>0 RETURN @@ERROR
 58 
 59 
 60 
 61 if @intCountProperties=0
 62 
 63 select '现在没有阻塞和死锁信息' as message
 64 
 65 
 66 
 67 -- 循环开始
 68 
 69 while @intCounter <= @intCountProperties
 70 
 71 begin
 72 
 73 -- 取第一条记录
 74 
 75 select @spid = spid,@bl = bl
 76 
 77 from #tmp_lock_who where Id = @intCounter
 78 
 79 begin
 80 
 81 if @spid =0
 82 
 83              select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
 84 
 85 else
 86 
 87              select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
 88 
 89 DBCC INPUTBUFFER (@bl )
 90 
 91 end
 92 
 93 
 94 
 95 -- 循环指针下移
 96 
 97 set @intCounter = @intCounter + 1
 98 
 99 end
100 
101 
102 
103 
104 
105 drop table #tmp_lock_who
106 
107 
108 
109 return 0
110 
111 end

 1 ////查看进程，处理进程
 2 
 3 
 4 ///关闭进程
 5 KILL 126
 6 
 7 
 8 
 9 ///查看进程情况
10 DBCC INPUTBUFFER(91)