A stored procedure that queries SQL Server database deadlock sharing _mssql

Using SQL Server as a database application system, can not avoid the sometimes deadlock, deadlock, maintenance personnel or developers will only through the sp_who to find the deadlock process, and then kill with Sp_kill. Using Sp_who_lock, this stored procedure makes it easy to know which process is deadlocked and where the deadlock problem is.

Creating Sp_who_lock Stored Procedures

CREATE procedure Sp_who_lock as BEGIN declare @spid int DECLARE @blk int declare @count int DECLA    
 Re @index int declare @lock tinyint set @lock =0 CREATE TABLE #temp_who_lock (ID int identity (1,1), SPID int, blk int) If @ @error <>0 return @ @error insert into #temp_who_lock (SPID,BLK) Selec T 0, blocked from (SELECT * from Master). sysprocesses where blocked>0) a Where NOT EXISTS (SELECT * from Master. sysprocesses where a.blocked =spid and blocked>0) union select spid,blocked from Master. sysprocesses where blocked>0 if @ @error <>0 return @ @error Select @count =count (*), @index =1 from #temp_who_ Lock if @ @error <>0 return @ @error if @count =0 begin select ' No blocking and deadlock information ' return 0 whil E @index <= @count begin IF exists (select 1 from #temp_who_lock a where id> @index and exists (select 1 from #te  Mp_who_lock where id<= @index and A.blk=spid)) 
 The Begin set @lock =1 select @spid =spid, @blk =blk from #temp_who_lock where id= @index select ' Cause the database to deadlock is: ' + C AST (@spid as VARCHAR (10)) + ' process number, whose execution SQL syntax is as follows ' select @spid, @blk DBCC INPUTBUFFER (@spid) DBCC INPUTBUFFER (@b LK) End Set @index = @index +1 "End If" @lock =0 begin set @index =1 while @index <= @count Begi n Select @spid =spid, @blk =blk from #temp_who_lock where id= @index if @spid =0 select ' causes blocking: ' +cast (@blk as V Archar (10)) + ' process number, whose execution SQL syntax follows ' ELSE SELECT ' Process number spid: ' + cast (@spid as VARCHAR (10)) + ' is ' + ' process number spid: ' + cast (@blk A   
 S VARCHAR (10)) + ' blocked, and its current process executes the SQL syntax as follows ' DBCC INPUTBUFFER (@spid) DBCC INPUTBUFFER (@blk) Set @index = @index +1
 End and drop table #temp_who_lock return 0

Executing in Query Analyzer:

EXEC Sp_who_lock

Until the end result is: * *