/************************************************************* Code formatted by SoftTree SQL Assistant ?v6.5.258* Time: 2014/11/12 5:46:41************************************************************/DECLARE @spid INTDECLARE @blk INTDECLARE @count INTDECLARE @index INTDECLARE @lock TINYINT SET @lock = 0 CREATE TABLE #temp_who_lock(id       INT IDENTITY(1, 1),spid     INT,blk      INT) --if @@error<>0 return @@error    INSERT 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 > 0 --if @@error<>0 return @@error    SELECT @count = COUNT(*),      @index = 1FROM   #temp_who_lock --select @count,@index --if @@error<>0 return @@error    IF @count = 0BEGIN   SELECT '没有阻塞和死锁信息'           --return 0END WHILE @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语法如下'               PRINT (LTRIM(@spid) + '' + LTRIM(@blk));       IF (@spid <> 0)       BEGIN           DBCC INPUTBUFFER(@spid) --       END               DBCC INPUTBUFFER(@blk) --引起阻塞语句               SET @index = @index + 1   ENDEND DROP TABLE #temp_who_lock --return 0    --KILL 64