sqlserver查看表是否被锁

CREATE<wbr>procedure [dbo].[sp_who_lock]<br>as<br>begin<br>declare @spid int,@bl int,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>@intTransactionCountOnEnt<wbr>ry<wbr>int,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>@intRowcount<wbr><wbr><wbr>int,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>@intCountProperties<wbr><wbr>int,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>@intCounter<wbr><wbr><wbr>int<br><wbr>create table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint)<br><wbr><br><wbr>IF @@ERROR&lt;&gt;0 RETURN @@ERROR<br><wbr><br><wbr>insert into #tmp_lock_who(spid,bl) select<wbr>0 ,blocked<br><wbr><wbr>from (select * from master..sysprocesses where<wbr>blocked&gt;0 ) a<br><wbr><wbr>where not exists(select * from (select * from master..sysprocesses where<wbr>blocked&gt;0 ) b<br><wbr><wbr>where a.blocked=spid)<br><wbr><wbr>union select spid,blocked from master..sysprocesses where<wbr>blocked&gt;0</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

<wbr>IF @@ERROR&lt;&gt;0 RETURN @@ERROR<br><wbr><br>-- 找到临时表的记录数<br><wbr>select<wbr>@intCountProperties = Count(*),@intCounter = 1<br><wbr>from #tmp_lock_who<br><wbr><br><wbr>IF @@ERROR&lt;&gt;0 RETURN @@ERROR<br><wbr><br><wbr>if @intCountProperties=0<br><wbr>select '现在没有阻塞和死锁信息' as message</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

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

-- 循环指针下移
<wbr>set @intCounter = @intCounter + 1<br>end</wbr>

drop table #tmp_lock_who

return 0
end