解决数据库死锁：sp_who_lock存储过程操作指南

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[sp_who_lock]    Script Date: 03/25/2013 11:17:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



create procedure [dbo].[sp_who_lock] 
as
declare @spid int,@bl integer,
        @intTransactionCountOnEntry integer,
        @intRowcount integer,
        @intCountProperties integer,
        @intCounter integer,
        @sp_lock integer,
        @t_long integer,
        @regdate datetime,
        @sp_lock_o integer
begin
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)