SQL2005 View Deadlock stored procedure sp_who_lock

Time:2019-5-3

The following is my monitoring SQL Server database, whether there are deadlocks and blockages in the performance testing process of the SQL statements, still relatively prepared, left for standby.

Call method: Select the corresponding database and exec sp_who_lock

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[sp_who_lock] 
as 
begin 
declare @spid int, @bl int, @intTransactionCountOnEntry int, @intRowcount int, @intCountProperties int, @intCounter int 

create table #tmp_lock_who ( 
	id int identity(1,1), 
	spid smallint, 
	bl smallint
) 

IF @@ERROR<>0 RETURN @@ERROR 

insert into #tmp_lock_who(spid,bl) select 0 ,blocked 
	from (select * from sysprocesses where blocked>0 ) a 
	where not exists(select * from (select * from sysprocesses where blocked>0 ) b 
	where a.blocked=spid) 
	union select spid,blocked from sysprocesses where blocked>0 

IF @@ERROR<>0 RETURN @@ERROR 
 
--Number of records found in temporary tables 
select @intCountProperties = Count(*),@intCounter = 1 
from #tmp_lock_who 

IF @@ERROR<>0 RETURN @@ERROR 

if @intCountProperties=0 
	Select'No blocking and deadlock information now'as message 

--The beginning of the cycle 
while @intCounter <= @intCountProperties 
begin 
	--Take the first record. 
	select @spid = spid,@bl = bl 
	from #tmp_lock_who where Id = @intCounter 
	begin 
		if @spid =0 
      Select'causes database deadlock by'+CAST (@bl AS VARCHAR(10)+'process number, which executes the following SQL syntax' ___________ 
		else 
      Select'process number SPID:'+ CAST (@spid AS VARCHAR(10)+'blocked by' +'process number SPID:'+ CAST (@bl AS VARCHAR(10)+'), whose current process executes the following SQL syntax' 
		DBCC INPUTBUFFER (@bl ) 
	end 

	-- Loop pointer down 
	set @intCounter = @intCounter + 1 
end 

drop table #tmp_lock_who 

return 0 
end