SET NOCOUNT ON
SET XACT_ABORT ON

if exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.#processes'))
	truncate table #processes
else
	create table #processes ( 
		[spid] [smallint] NULL, 
		[blocked] [smallint] NULL, 
		[name] [nvarchar] ( 128 ) NULL,
		[waittime] [bigint] NULL ,
		[lastwaittype] [nchar] ( 32 ) NULL ,
		[waitresource] [nchar] ( 256 ) NULL ,
		[open_tran] [smallint] NULL ,
		[status] [nchar] ( 30 ) NULL ,
		[hostname] [nchar] ( 128 ) NULL ,
		[program_name] [nchar] ( 128 ) NULL ,
		[cmd] [nchar] ( 16 ) NULL ,
		[loginame] [nchar] ( 128 ) NULL ,
		[cpu] [int] NULL ,
		[physical_io] [bigint] NULL ,
		[memusage] [int] NULL,
		[last_batch] [datetime] NULL,
		[sql] [nvarchar] ( 500 ) NULL
	)

insert into #processes
	select spid, blocked, d.name, waittime, lastwaittype, waitresource, open_tran, p.status, 
		hostname, program_name, cmd, loginame, cpu, physical_io, memusage, last_batch, ''
	from master.dbo.sysprocesses p left outer join master..sysdatabases d on d.dbid = p.dbid

if NOT exists( select * from #processes where ISNULL( blocked,  0 ) <>  0 )
begin
	select * from #processes 
	order by blocked, waittime
	return
end

if exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.#last_sql'))
	truncate table #last_sql
else
	create table #last_sql ( EventType nvarchar( 30 ), Parameters int, EventInfo nvarchar( 500 ))

DECLARE cur_blocked CURSOR FAST_FORWARD FOR
	select spid from #processes 
	where ISNULL( blocked,  0 ) <>  0 
		OR spid in ( select blocked from #processes)

declare @spid int, @cmd varchar( 100 )

OPEN cur_blocked 

FETCH NEXT FROM cur_blocked INTO @spid
WHILE @@fetch_status =  0 
BEGIN
	truncate table #last_sql
	set @cmd = 'DBCC INPUTBUFFER (' + CAST(@spid as varchar( 12 )) + ')'
	insert into #last_sql ( EventType, Parameters, EventInfo)
		exec (@cmd)

	update #processes set sql = ISNULL( (select EventInfo from #last_sql), '')
	where spid = @spid

	FETCH NEXT FROM cur_blocked INTO @spid
END

CLOSE cur_blocked 
DEALLOCATE cur_blocked 

select * from #processes
order by blocked, waittime