View the current process, or the deadlock process, and automatically kill the process

Process

/*--Handle Deadlocks

View the current process, or the deadlock process, and automatically kill the process

Because it is dead, so if there is a deadlock process, only the deadlock process can be viewed
Of course, you can control by parameter, whether or not deadlock, only check the view lock process

--Jiangjian 2004.4--*/

/*--Call Example

EXEC P_lockinfo
--*/
Create proc P_lockinfo
@kill_lock_spid Bit=1,--whether to kill the deadlock process, 1 kill, 0 show only
@show_spid_if_nolock Bit=1-If no deadlock process, display normal process information, 1 display, 0 does not show
As
DECLARE @count int,@s nvarchar (1000), @i int
Select Id=identity (int,1,1), logo,
Process Id=spid, thread id=kpid, block process id=blocked, database id=dbid,
Database name =db_name (dbid), user Id=uid, username =loginame, Cumulative CPU time =CPU,
Login time =login_time, open transaction number =open_tran, process state =status,
Workstation name =hostname, application name =program_name, workstation process id=hostprocess,
Domain =nt_domain, network card address =net_address
Into #t from (
Select flag = ' Deadlock process ',
Spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
Status,hostname,program_name,hostprocess,nt_domain,net_address,
S1=a.spid,s2=0
From Master.. sysprocesses a Join (
Select blocked from master. sysprocesses GROUP by blocked
) b on a.spid=b.blocked where a.blocked=0
UNION ALL
Select ' |_ victim _> ',
Spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
Status,hostname,program_name,hostprocess,nt_domain,net_address,
S1=blocked,s2=1
From Master.. sysprocesses a where blocked<>0
) A ORDER by s1,s2

Select @count =@ @rowcount, @i=1

If @count =0 and @show_spid_if_nolock =1
Begin
Insert #t
Select flag = ' normal process ',
Spid,kpid,blocked,dbid,db_name (dbid), Uid,loginame,cpu,login_time,
Open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
From Master.. sysprocesses
Set @count =@ @rowcount
End

If @count >0
Begin
CREATE table #t1 (ID int identity (1,1), a nvarchar (), b int,eventinfo nvarchar (255))
If @kill_lock_spid =1
Begin
DECLARE @spid varchar (10), @ Logo varchar (10)
While @i<= @count
Begin
Select @spid = Process id,@ flag = flag from #t whereid=@i
Insert #t1 EXEC (' DBCC INPUTBUFFER (' + @spid + ') ')
IF @ flag = ' deadlock process ' exec (' kill' + @spid)
Set @i=@i+1
End
End
Else
While @i<= @count
Begin
Select @s= ' DBCC INPUTBUFFER (' +cast (process ID as varchar) + ') ' from #t whereid=@i
Insert #t1 EXEC (@s)
Set @i=@i+1
End
Select a.*, the SQL statement of the process =b.eventinfo
From #t a join #t1 B on a.id