一个查死锁的存储过程

set ANSI_NULLSON
set QUOTED_IDENTIFIERON
go

createprocedure[dbo].[sp_who_lock]
as
begin
declare@spidint,@blint,
@intTransactionCountOnEntry int,
        @intRowcount   int,
        @intCountProperties  int,
        @intCounter   int

createtable #tmp_lock_who (
id intidentity(1,1),
spid smallint,
bl smallint)

IF @@ERROR<>0RETURN@@ERROR

insertinto #tmp_lock_who(spid,bl)select 0 ,blocked
   from (select*from sysprocesseswhere  blocked>0 ) a
   wherenotexists(select*from (select*from sysprocesseswhere  blocked>0 ) b
   where a.blocked=spid)
   unionselect spid,blockedfrom sysprocesseswhere  blocked>0

IF @@ERROR<>0RETURN@@ERROR
 
-- 找到临时表的记录数
select @intCountProperties=Count(*),@intCounter=1
from #tmp_lock_who

IF @@ERROR<>0RETURN@@ERROR

if @intCountProperties=0
  select'现在没有阻塞和死锁信息'as message

-- 循环开始
while@intCounter<=@intCountProperties
begin
-- 取第一条记录
  select @spid= spid,@bl= bl
  from #tmp_lock_whowhere Id=@intCounter
begin
  if@spid=0
            select'引起数据库死锁的是:'+CAST(@blAS VARCHAR(10))+'进程号,其执行的SQL语法如下'
else
            select'进程号SPID：'+CAST(@spidAS VARCHAR(10))+'被'+'进程号SPID：'+CAST(@blAS VARCHAR(10))+'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end

-- 循环指针下移
set@intCounter=@intCounter+1
end

droptable #tmp_lock_who

return0
end