IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'sp_Lock_Scan')
     DROP PROCEDURE sp_Lock_Scan
 GO
 
 CREATE PROCEDURE sp_Lock_Scan
 AS
    DECLARE @SPID INT
    DECLARE @BLK INT
     DECLARE @Count INT
     DECLARE @Counter INT
     DECLARE @LOCK BIT
 
    CREATE TABLE #Temp
    (
         [Id] INT IDENTITY
        ,[SPID] INT
         ,[BLOCK] INT
     )

    SELECT @LOCK = 0

     IF @@ERROR <> 0 RETURN @@ERROR
 
     INSERT INTO #Temp
    (
         [SPID], [BLOCK]
    )
     SELECT
         0, [blocked]
    FROM
        (
           SELECT * FROM [master]..[sysprocesses] WHERE [blocked] > 0
         ) a
     WHERE
        NOT EXISTS
        (
            SELECT * FROM [master]..[sysprocesses] WHERE a.[blocked] = [spid] AND [blocked] > 0
         )
     UNION
       SELECT [spid], [blocked] FROM [master]..[sysprocesses] WHERE [blocked] > 0

    IF @@ERROR <> 0 RETURN @@ERROR
 
     SELECT @Count = COUNT(*), @Counter = 1 FROM #Temp
 
     IF @@ERROR <> 0 RETURN @@ERROR

     IF @Count = 0
        BEGIN
             SELECT N'没有阻塞和死锁信息' [ScanMessage]
             RETURN 0
         END
     ELSE
        BEGIN
             WHILE @Counter <= @Count
                BEGIN
                     IF EXISTS
                      (
                            SELECT * FROM #Temp a 
                            WHERE
                                a.[Id] > @Counter
                             AND
                                EXISTS
                               (
                                    SELECT * FROM #Temp WHERE [Id] <= @Counter AND a.[BLOCK] = [SPID]
                                 )
                        )
                        BEGIN
                             SELECT @LOCK = 1

                            SELECT @SPID = [SPID], @BLK = [BLOCK] from #Temp WHERE [Id] = @Counter

                           SELECT N'引起数据库死锁的是：【' + CAST(@SPID AS NVARCHAR(255)) + N'】进程,其执行的SQL语言如下' [ScanMessage]
 
                            SELECT @SPID [SPID], @BLK [BLOCKED]
 
                             DBCC INPUTBUFFER(@SPID)
                             DBCC INPUTBUFFER(@BLK)
                         END
                    SELECT @Counter = @Counter + 1
                END

             IF @LOCK = 0
               BEGIN
                   SELECT @Counter = 1
 
                    WHILE @Counter <= @Count
                        BEGIN
                            SELECT @SPID = [SPID], @BLK = [BLOCK] FROM #Temp where [Id] = @Counter

                            IF @SPID = 0
                                 SELECT N'引起阻塞的是：【'+ CAST(@BLK AS NVARCHAR(255)) + '】，其执行的SQL语法如下' [ScanMessage]
                            ELSE
                               SELECT N'进程【' + CAST(@SPID AS NVARCHAR(255)) + N'】被进程【' + CAST(@BLK AS NVARCHAR(255)) + N'】阻塞，当前进程的SQL语法如下' [ScanMessage]

                            DBCC INPUTBUFFER(@SPID)
                            DBCC INPUTBUFFER(@BLK)

                           SELECT @Counter = @Counter + 1
                        END
                END
        END
RETURN 0
GO

?