Use [master]go/****** Object: storedprocedure [dbo]. [Sp_who_lock] script date:07/30/2015 13:52:38 ******/set ansi_nulls ongoset quoted_identifier ONGOALTER Pro cedure [dbo]. [Sp_who_lock]asbegindeclare @spid int, @bl int, @intTransactionCountOnEntry int, @intRowcount int, @intCountProperties int, @intCounter Intcreate table #tmp_lock_who (id int identity (), spid smallint,bl smallint) if @ @ERROR <>0 RETURN @ @ERROR & Nbsp;insert to #tmp_lock_who (SPID,BL) Select 0, Blockedfrom (SELECT * from sysprocesses where blocked>0) Awhere not Exi STS (SELECT * from sysprocesses where blocked>0) b where a.blocked=spid) union select spid,blocked from SYS Processes where blocked>0if @ @ERROR <>0 RETURN @ @ERROR --Find the number of records for the staging table Select @intCountProperties = Count (*), @intCounter = 1from #tmp_lock_who if @ @ERROR <>0 RETURN @ @ERROR if @intCountProperties =0select ' now has no blocking and deadlock information ' as message--cycle starts while @intCounter <= @intCountPropertiesbegin-- Take the first record select @spid = spid, @bl =bl from #tmp_lock_who where [email protected]beginif @spid =0 select ' The resulting database deadlock is: ' + cast (@bl as varchar (10) + ' process number, which executes the SQL syntax as follows ' else select ' process number spid: ' + cast (@spid as varchar (10) + ' + ') The process number spid: ' + CAST (@bl as VARCHAR (10)) + ' block, its current process executes the SQL syntax as follows ' DBCC InputBuffer (@bl) end--The loop pointer moves down set @intCounter = @intCounter + 1enddrop Table #tmp_lock_whoreturn 0end
SQL Server 2008 viewing deadlock stored procedures
