Sql Server查询死锁信息

存储过程，执行时确定是否处理死锁和显示死锁信息

ALTER PROC [dbo].[p_lockinfo]
    @kill_lock_spid BIT = 0 ,     --是否杀掉死锁的进程,1   杀掉,   0   仅显示   
    @show_spid_if_nolock BIT = 1   --如果没有死锁的进程,是否显示正常进程信息,1   显示,0   不显示   
AS
    DECLARE 
		@count INT ,
        @s NVARCHAR(4000) ,
        @i INT   
    SELECT  id = IDENTITY( INT,1,1 ),
            标志 ,
            进程ID = spid ,
            线程ID = kpid ,
            块进程ID = blocked ,
            数据库ID = dbid ,
            数据库名 = DB_NAME(dbid) ,
            用户ID = uid ,
            用户名 = loginame ,
            累计CPU时间 = cpu ,
            登陆时间 = login_time ,
            打开事务数 = open_tran ,
            进程状态 = status ,
            工作站名 = hostname ,
            应用程序名 = program_name ,
            工作站进程ID = hostprocess ,
            域名 = nt_domain ,
            网卡地址 = net_address
    INTO    #t
    FROM    ( SELECT    标志 = '死锁的进程' ,
                        spid ,
                        kpid ,
                        a.blocked ,
                        dbid ,
                        uid ,
                        loginame ,
                        cpu ,
                        login_time ,
                        open_tran ,
                        status ,
                        hostname ,
                        program_name ,
                        hostprocess ,
                        nt_domain ,
                        net_address ,
                        s1 = a.spid ,
                        s2 = 0
              FROM      master..sysprocesses a
                        JOIN ( SELECT blocked FROM master..sysprocesses GROUP BY blocked ) b ON a.spid = b.blocked
              WHERE     a.blocked = 0
              UNION   ALL
              SELECT    '|_牺牲品_>' ,
                        spid ,
                        kpid ,
                        blocked ,
                        dbid ,
                        uid ,
                        loginame ,
                        cpu ,
                        login_time ,
                        open_tran ,
                        status ,
                        hostname ,
                        program_name ,
                        hostprocess ,
                        nt_domain ,
                        net_address ,
                        s1 = blocked ,
                        s2 = 1
              FROM      master..sysprocesses a
              WHERE     blocked <> 0
            ) a
    ORDER BY s1 ,s2   
    
    SELECT  @count = @@rowcount ,
            @i = 1   
    IF @count = 0 AND @show_spid_if_nolock = 1
        BEGIN   
            INSERT  #t
                    SELECT  标志 = '正常的进程' ,
                            spid ,
                            kpid ,
                            blocked ,
                            dbid ,
                            DB_NAME(dbid) ,
                            uid ,
                            loginame ,
                            cpu ,
                            login_time ,
                            open_tran ,
                            status ,
                            hostname ,
                            program_name ,
                            hostprocess ,
                            nt_domain ,
                            net_address
                    FROM    master..sysprocesses   
            SET @count = @@rowcount   
        END   
  
    IF @count = 0 AND @show_spid_if_nolock = 0
        BEGIN       
            SELECT * FROM #t
            RETURN          
        END
  
    IF @count > 0
        BEGIN   
            CREATE   TABLE #t1
                (
                  id INT IDENTITY(1, 1) ,
                  a NVARCHAR(4000) ,
                  b INT ,
                  EventInfo NVARCHAR(4000)
                )   
            IF @kill_lock_spid = 1
                BEGIN   
                    DECLARE 
						@spid VARCHAR(200) ,
                        @标志 VARCHAR(200)   
                    WHILE @i <= @count
                        BEGIN   
                            SELECT @spid = 进程ID , @标志 = 标志 FROM #t WHERE id = @i   
                            INSERT #t1 EXEC ( 'dbcc inputbuffer(' + @spid + ')' )   
                            IF @标志 = '死锁的进程'
								EXEC('kill '+@spid)   
                            SET @i = @i + 1   
                        END   
                END   
            ELSE
					WHILE @i <= @count
						BEGIN   
							SELECT @s = 'dbcc inputbuffer(' + CAST(进程ID AS VARCHAR) + ')' FROM #t WHERE id = @i   
							INSERT #t1 EXEC ( @s )   
							SET @i = @i + 1   
						END   
            SELECT  a.* ,
                    进程的SQL语句 = b.EventInfo
            FROM    #t a
                    JOIN #t1 b ON a.id = b.id   
        END