USE master
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 
  
-- 找到临时表的记录数
        SELECT  @intCountProperties = COUNT(*) ,
                @intCounter = 1
        FROM    #tmp_lock_who
 
        IF @@ERROR <> 0 
            RETURN @@ERROR 
 
        IF @intCountProperties = 0 
            SELECT  '现在没有阻塞和死锁信息' AS message

-- 循环开始
        WHILE @intCounter <= @intCountProperties 
            BEGIN
-- 取第一条记录
                SELECT  @spid = spid ,
                        @bl = bl
                FROM    #tmp_lock_who
                WHERE   Id = @intCounter 
                BEGIN
                    IF @spid = 0 
                        SELECT  '引起数据库死锁的是: ' + CAST(@bl AS VARCHAR(10))
                                + '进程号,其执行的SQL语法如下'
                    ELSE 
                        SELECT  '进程号SPID：' + CAST(@spid AS VARCHAR(10)) + '被'
                                + '进程号SPID：' + CAST(@bl AS VARCHAR(10))
                                + '阻塞,其当前进程执行的SQL语法如下'
                    DBCC INPUTBUFFER (@bl )
                END 

-- 循环指针下移
                SET @intCounter = @intCounter + 1
            END

        DROP TABLE #tmp_lock_who

        RETURN 0
    END