SQL Server deadlock Check Tool

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [sp_who_lock] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [sp_who_lock]
Go
/*************************************** ************************************
// Create: Fengyu mail: maggiefengyu@tom.com Date:
// Modify: From http://www.csdn.net/develop/Read_Article.asp? Id = 26566 learned and rewritten
// Description: view the blocking and deadlock conditions in the database
**************************************** ***********************************/
Use master
Go
Create procedure 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. blocked = spid)
Union select spid, blocked from sysprocesses where blocked> 0

If @ error <> 0 return @ error
-- find the number of records in the temporary table
select @ intcountproperties = count (*), @ intcounter = 1
from # tmp_lock_who
If @ error <> 0 return @ error
If @ intcountproperties = 0
select 'no blocking and deadlock information' as message

-- Start of Loop
While @ intcounter <= @ intcountproperties
Begin
-- Retrieve the first record
Select @ spid = spid, @ BL = bl
From # tmp_lock_who where id = @ intcounter
Begin
If @ spid = 0
Select '+ Cast (@ BL as varchar (10) +' causes database deadlock. The SQL statement executed by 'process No. 'is as follows'
Else
Select 'process No. spid: '+ Cast (@ spid as varchar (10) +' quilt '+' process no. spid: '+ Cast (@ BL as varchar (10 )) + 'blocking, the SQL syntax executed by the current process is as follows'
DBCC inputbuffer (@ BL)
End

-- Move the loop pointer down
Set @ intcounter = @ intcounter + 1
End

Drop table # tmp_lock_who

Return 0
End