The process of looking for a locked table

The following procedure can find the deadlock process

use [ksoa]goset ansi_nulls ongoset quoted_identifier ongocreate procedure [ DBO]. [Sp_who_lock] asbegindeclare  @spid  int, @bl  int, @intTransactionCountOnEntryint, @intRowcountint, @ Intcountpropertiesint, @intCounterintcreate  table  #tmp_lock_who   ( id int identity (,  spid smallint,  bl smallint ) if @ @ERROR <>0 return  @ @ERRORinsert  into  #tmp_lock_who (SPID,BL)  select 0, blockedfrom  (select  * from master.dbo.sysprocesses where  blocked>0 )  awhere not  EXISTS  (select * from  (select * from master.dbo.sysprocesses  where  blocked>0)  b where spid=a.blocked) union select spid,blocked  from master.dbo.sysprocesses where  blocked>0if @ @ERROR <>0 RETURN  @ @ERROR-- Number of records found for temporary table select@intcountproperties = count (*),  @intCounter  = 1from  #tmp_lock_ whoif @ @ERROR <>0 return @ @ERRORif @intcountproperties = 0select  ' No blocking and deadlock information now! '  as MESSAGE--  cycle begins while  @intCounter  <=  @intCountPropertiesBEGIN--  Take the first record select@spid = spid,  @bl  = blfrom  #tmp_lock_who  where id =   @intCounterbeginif   @spid  = 0select  ' cause database deadlock is:   process number ' + cast (@bl   As varchar  +  ',  The SQL syntax it executes is as follows: ' elseselect  ' process number spid: ' + cast (@spid  as  varchar () +  ' is blocked by the process number spid: ' + cast (@bl  as varchar ()  + ') and its current process executes the following SQL syntax: ' DBCC  INPUTBUFFER  (@bl  ) end--  loop pointer down set  @intCounter  =  @intCounter  +  1enddrop table  #tmp_lock_whoreturn  0end

The process of looking for a locked table