查询Sqlserver数据库死锁的一个存储过程

原文： 点击打开链接

/************************************************************ * Code formatted by SoftTree SQL Assistant ?v6.1.35 * Time: 2013/11/12 16:48:00 ************************************************************/IF OBJECT_ID('proc_who_lock') IS NOT NULLBEGIN    DROP PROC proc_who_lockENDGOCREATE PROCEDURE proc_who_lockASBEGINDECLARE @spid INT     DECLARE @blk INT     DECLARE @count INT     DECLARE @index INT     DECLARE @lock TINYINT      SET @lock = 0      CREATE TABLE #temp_who_lock(id       INT IDENTITY(1, 1),spid     INT,blk      INT)      IF @@error <> 0    RETURN @@errorINSERT INTO #temp_who_lock  (    spid,    blk  )SELECT 0,       blockedFROM   (           SELECT *           FROM   MASTER..sysprocesses           WHERE  blocked > 0       )aWHERE  NOT EXISTS(           SELECT *           FROM   MASTER..sysprocesses           WHERE  a.blocked = spid                  AND blocked > 0       ) UNIONSELECT spid,       blockedFROM   MASTER..sysprocessesWHERE  blocked > 0IF @@error <> 0    RETURN @@errorSELECT @count = COUNT(*),       @index = 1FROM   #temp_who_lockIF @@error <> 0    RETURN @@errorIF @count = 0BEGIN    SELECT '没有阻塞和死锁信息'     RETURN 0ENDWHILE @index <= @countBEGIN    IF EXISTS(           SELECT 1           FROM   #temp_who_lock a           WHERE  id > @index                  AND EXISTS(                          SELECT 1                          FROM   #temp_who_lock                          WHERE  id <= @index                                 AND a.blk = spid                      )       )    BEGIN        SET @lock = 1              SELECT @spid = spid,               @blk = blk        FROM   #temp_who_lock        WHERE  id = @index                SELECT '引起数据库死锁的是: ' + CAST(@spid AS VARCHAR(10)) +                '进程号,其执行的SQL语法如下'                SELECT @spid,               @blk                DBCC INPUTBUFFER(@spid)         DBCC INPUTBUFFER(@blk)    END        SET @index = @index + 1END     IF @lock = 0BEGIN    SET @index = 1          WHILE @index <= @count    BEGIN        SELECT @spid = spid,               @blk = blk        FROM   #temp_who_lock        WHERE  id = @index                IF @spid = 0            SELECT '引起阻塞的是:' + CAST(@blk AS VARCHAR(10)) +                    '进程号,其执行的SQL语法如下'        ELSE            SELECT '进程号SPID：' + CAST(@spid AS VARCHAR(10)) + '被' +                    '进程号SPID：' + CAST(@blk AS VARCHAR(10)) +                    '阻塞,其当前进程执行的SQL语法如下'                DBCC INPUTBUFFER(@spid)         DBCC INPUTBUFFER(@blk)              SET @index = @index + 1    ENDENDDROP TABLE #temp_who_lock RETURN 0ENDGO EXEC proc_who_lock