Find Blocked SQL and Blocked By Information.

Create Proc SP_GetBlockedInfo
as

declare @spid                 int
declare @blocked             int
declare @db_name             varchar(500)
declare @program_name         varchar(1000)
declare @loginame             varchar(500)
declare @login_time         datetime
declare @last_batch         datetime
declare @str                 varchar(100)
declare @runtimeinmin         int
declare @min int
set @min = 1

create table #bufferspid(eventtype varchar(200)
,parameters varchar(200)
,eventinfo varchar(7600))

create table #bufferblocked(eventtype varchar(200)
,parameters varchar(200)
,eventinfo varchar(7600))

create table #blocked( 
    row_id                int identity (1,1)
    ,spid                int
    ,sqlspid            varchar(3000)
    ,blockedby            int
    ,sqlblockedby        varchar(3000)
    ,servername            varchar(200)
    ,dbname                varchar(200)
    ,programname        varchar(200)
    ,loginame            varchar(500)
    ,login_time            datetime
    ,last_batch            datetime
    ,runtimeinmin        int)
IF Exists (select * from master..syscursors with (nolock) where cursor_name = 'cur_blocked_spid')
Begin
    close cur_blocked_spid
    deallocate cur_blocked_spid
    End
    declare cur_blocked_spid cursor
    read_only
    for select 
                spid
                ,blocked
                ,db_name(dbid)
                ,program_name
                ,loginame
                ,login_time
                ,last_batch 
                ,datediff(mi,last_batch,getdate()) runtimeinmin
    from 
            master.dbo.sysprocesses with (nolock)
    where 
            blocked > 0
    open cur_blocked_spid
    fetch next from cur_blocked_spid into @spid,@blocked,@db_name,@program_name,@loginame,@login_time,@last_batch,@runtimeinmin
    while (@@fetch_status <> -1)
    begin
        if (@@fetch_status <> -2)
        begin
        set @str = 'dbcc inputbuffer ('+convert(varchar,@spid)+')'
        insert #bufferspid
        exec (@str)
        set @str = 'dbcc inputbuffer ('+convert(varchar,@blocked)+')'
        insert #bufferblocked
        exec (@str)
        insert into #blocked (spid,sqlspid,blockedby,sqlblockedby,servername,dbname,programname,loginame,login_time,last_batch,runtimeinmin)
        select
                @spid as spid
                ,(select eventinfo from #bufferspid) as sqlspid 
                ,@blocked as blockedby
                ,(select eventinfo from #bufferblocked) as sqlblockedby
                ,@@servername as servername
                ,@db_name as dbname
                ,@program_name as programname
                ,@loginame as loginame
                ,@login_time as login_time
                ,@last_batch as last_batch 
                ,@runtimeinmin as runtimeinmin
        truncate table #bufferspid
        truncate table #bufferblocked
    end
fetch next from cur_blocked_spid into @spid,@blocked,@db_name,@program_name,@loginame,@login_time,@last_batch,@runtimeinmin
end
close cur_blocked_spid
deallocate cur_blocked_spid

select 
        spid
        ,programname
        ,sqlspid
        ,blockedby
        ,sqlblockedby
        ,servername
        ,dbname,loginame
        ,login_time
        ,last_batch
        ,runtimeinmin 
from #blocked

-- when testing with query analyzer open the following code.
DROP TABLE #BUFFERSPID
DROP TABLE #BUFFERBLOCKED
DROP TABLE #BLOCKED