I think you misunderstand the question... but I figured it out some time ago. this is what I'm doing:
USE [DBA]
GO
/****** Object: Table [dbo].[blockingreport2013] Script Date: 01/16/2014 06:56:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[blockingreport2013](
[waitresourcetype] [varchar](500) NULL,
[waitresourcedatabase] [nvarchar](128) NULL,
[waitresourceid] [varchar](507) NULL,
[WAITTABLENAME] [nvarchar](500) NULL,
[WAITINDEXNAME] [varchar](500) NULL,
[starttime] [varchar](50) NULL,
[waittime] [varchar](1000) NULL,
[waitresource] [varchar](500) NULL,
[objectid] [varchar](500) NULL,
[databaseid] [varchar](10) NULL,
[indexid] [varchar](10) NULL,
[blockingdatabasename] [varchar](500) NULL,
[blockingobjectname] [varchar](4000) NULL,
[blockeddatabasename] [varchar](500) NULL,
[blockedobjectname] [varchar](4000) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [DBA]
GO
/****** Object: StoredProcedure [dbo].[Record_blockedProcesses] Script Date: 01/16/2014 06:49:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc
[dbo].[Record_blockedProcesses]
@pathandfilename varchar (500)=null
as
begin
--declare @pathandfilename varchar (500)
--set @pathandfilename='d:\trace_blockedprocesses_2013-12-10_0400.trc'
if (@pathandfilename is null)
begin
select @pathandfilename=path from sys.traces where path like '%blocked%'
end
select
WaitTime = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@waittime','varchar(20)' ),
WaitResource = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@waitresource','varchar(50)' ),
BlockingQuery = cast( textdata as xml ).value( '(/blocked-process-report/blocking-process/process/inputbuf)[1]','varchar(200)'),
BlockingSpid = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/@spid','varchar(20)' ),
BlockingLastBatchStarted = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/@lastbatchstarted','varchar(50)' ),
BlockingSQLHandle = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@sqlhandle','varchar(50)' ),
BlockingStmtstart = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@stmtstart','varchar(50)' ),
BlockingStmtend = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@stmtend','varchar(50)' ),
BlockedQuery = cast( textdata as xml ).value( '(/blocked-process-report/blocked-process/process/inputbuf)[1]','varchar(200)'),
BlockedSpid = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@spid','varchar(20)' ),
BlockedLastBatchStarted = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@lastbatchstarted','varchar(50)' ),
BlockedSQLHandle = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@sqlhandle','varchar(50)' ),
BlockedStmtstart = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@stmtstart','varchar(50)' ),
BlockedStmtend = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@stmtend','varchar(50)' ),
OBJECTID,IndexID,databaseid
into #temp1
from fn_trace_gettable (@pathandfilename, default)
where eventclass = 137
and TextData not like '%Database Id = 32767%'
order by BlockedLastBatchStarted
/**
select
db_name(cast (Right(LEFT([blockingquery],23),2) as int)) as blockingdatabasename
,replace (replace( (Right(blockingquery,(CHARINDEX('Object Id = ',BlockingQuery)-10))),'=',''),']','')as blockingobjectid
,db_name(cast (Right(LEFT([blockedquery],23),2) as int)) as blockeddatabasename
,replace (replace( (Right(blockedquery,(CHARINDEX('Object Id = ',BlockedQuery)-10))),'=',''),']','')as blockedobjectid,
*
from #temp1**/
declare @blockingdatabasename varchar(100), @blockingobjectid varchar(100),@sql nvarchar(4000)
declare @blockeddatabasename varchar(100), @blockedobjectid varchar(100)
declare @blockedquery varchar(4000), @blockingquery varchar(4000),@waitresource varchar(500)
declare @results table(starttime varchar(50),
waittime varchar(1000),
waitresource varchar(500),
objectid varchar(500)
,databaseid varchar(10)
,indexid varchar(10)
,blockingdatabasename varchar(500)
,blockingobjectname varchar(4000)
,blockeddatabasename varchar(500)
,blockedobjectname varchar(4000)
)
declare @waittime varchar(1000)
declare @BlockingLastBatchStarted varchar(50)
declare @objectid varchar(500), @databaseid varchar(10), @indexid varchar(10)
declare dbcursor cursor for
select
BlockingLastBatchStarted,
waittime,
waitresource,
CASE
WHEN blockingquery like '%Proc %' THEN
db_name(cast (Right(LEFT([blockingquery],23),2) as int))
ELSE 'NA'
end
,CASE
WHEN blockingquery like '%Proc %' THEN
replace (replace( (Right(blockingquery,(CHARINDEX('Object Id = ',BlockingQuery)-10))),'=',''),']','')
ELSE '-9999'
END
,CASE
WHEN blockedquery like '%Proc %' THEN
db_name(cast (Right(LEFT([blockedquery],23),2) as int))
ELSE 'NA'
END
,CASE
WHEN blockedquery like '%Proc %' THEN
replace (replace( (Right(blockedquery,(CHARINDEX('Object Id = ',BlockedQuery)-10))),'=',''),']','')
ELSE '-9999'
END
,blockingquery,blockedquery,objectid,indexid,databaseid
from #temp1
open dbcursor
fetch next from dbcursor into @BlockingLastBatchStarted,@waittime,@waitresource,@blockingdatabasename,@blockingobjectid,@blockeddatabasename,@blockedobjectid,@blockingquery,@blockedquery,@objectid,@indexid,@databaseid
while @@FETCH_STATUS =0
begin
--select @blockingdatabasename,@blockingobjectid,@blockeddatabasename,@blockedobjectid
if (@blockingdatabasename='NA' and @blockeddatabasename!='NA')
begin
set @sql= 'select '''+@BlockingLastBatchStarted+''','+@waittime+','''+@waitresource+''','''+@objectid+''','''+@databaseid+''','''+@indexid+''', ''NA'','''+ REPLACE(@blockingquery, '''', '"')+''' ,'''+@blockeddatabasename+''',b.name from '+@blockeddatabasename+'.dbo.sysobjects b where b.id='+@blockedobjectid
--select @sql
insert @results
exec (@sql)
end
if (@blockingdatabasename!='NA' and @blockeddatabasename!='NA')
begin
set @sql= 'select '''+@BlockingLastBatchStarted+''','+@waittime+','''+@waitresource+''','''+@objectid+''','''+@databaseid+''','''+@indexid+''', '''+@blockingdatabasename+''',a.name ,'''+@blockeddatabasename+''',b.name from '+@blockingdatabasename+'.dbo.sysobjects a, '+@blockeddatabasename+'.dbo.sysobjects b where a.id='+@blockingobjectid+'and b.id='+@blockedobjectid
--select @sql
insert @results
exec (@sql)
end
if (@blockingdatabasename!='NA' and @blockeddatabasename='NA')
begin
set @sql= 'select '''+@BlockingLastBatchStarted+''','+@waittime+','''+@waitresource+''','''+@objectid+''','''+@databaseid+''','''+@indexid+''', '''+@blockingdatabasename+''',a.name ,''NA'',''"'+REPLACE(@blockedquery, '''', '"')+'"'' from '+@blockingdatabasename+'.dbo.sysobjects a where a.id='+@blockingobjectid
--select @sql
insert @results
exec (@sql)
end
if (@blockingdatabasename='NA' and @blockeddatabasename='NA')
begin
set @sql= 'select '''+@BlockingLastBatchStarted+''','+@waittime+','''+@waitresource+''','''+@objectid+''','''+@databaseid+''','''+@indexid+''', ''NA'',''"'+REPLACE(@blockedquery, '''', '"')+'"'',''NA'','''+ REPLACE(@blockingquery, '''', '"')+''''
--select @sql
insert @results
exec (@sql)
end
fetch next from dbcursor into @BlockingLastBatchStarted,@waittime,@waitresource,@blockingdatabasename,@blockingobjectid,@blockeddatabasename,@blockedobjectid,@blockingquery,@blockedquery,@objectid,@indexid,@databaseid
end
select * into #temp3 from @results
order by starttime
close dbcursor
deallocate dbcursor
drop table #temp1
--end
select LEFT(waitresource,(charindex(':',waitresource))-1 ) as waitresourcetype,
DB_NAME(
CASE WHEN waitresource like 'KEY%' then
SUBSTRING( waitresource,
CHARINDEX(':', waitresource) + 1,
LEN(waitresource) - CHARINDEX(':', waitresource) - CHARINDEX(':', REVERSE(waitresource))
)
ELSE
LEFT(
SUBSTRING( waitresource,
CHARINDEX(':', waitresource) + 1,
LEN(waitresource) - CHARINDEX(':', waitresource) - CHARINDEX(':', REVERSE(waitresource))
),
(charindex(':',SUBSTRING( waitresource,
CHARINDEX(':', waitresource) + 1,
LEN(waitresource) - CHARINDEX(':', waitresource) - CHARINDEX(':', REVERSE(waitresource))
)
))-1)
END) as waitresourcedatabase,
CASE WHEN waitresource like 'PAGE%' then waitresource
WHEN waitresource like 'KEY%' then
LEFT(
RIGHT(waitresource,charindex(':',reverse(waitresource))-1)
,CHARINDEX (' ', RIGHT(waitresource,charindex(':',reverse(waitresource))-1)))
WHEN waitresource like 'OBJECT%' then
objectid
ELSE 'OTHER -'+waitresource
END
AS waitresourceid
,CASE when waitresource like 'OBJECT%' then
OBJECT_NAME(objectid,databaseid)
When waitresource like 'KEY%' then 'notdone'
When waitresource like 'PAGE' then waitresource
end
as WAITTABLENAME
,'page_or_object_lock_no_index_captured' as WAITINDEXNAME,*
into #temp4
from #temp3
drop table #temp3
--select * from #temp4
-------------------------------------------
--begin block to update waitresource columns
----
declare @sql2 nvarchar(400)
declare updatecursor cursor for
select waitresourcedatabase, waitresourceid,waittablename from #temp4
where waittablename='notdone'
declare @waittable varchar(200)
declare @waitindex varchar(500)
declare @waitresourcedatabase varchar(200)
declare @waitresourceid varchar(200)
DECLARE @ParmDefinition nvarchar(500)
open updatecursor
fetch next from updatecursor into @waitresourcedatabase, @waitresourceid,@waittable
while @@FETCH_STATUS =0
begin
--select @waitresourceid
SET @ParmDefinition = N' @waitresourceidIN varchar(200), @waittableout nvarchar(25) OUTPUT'
set @sql2='SELECT @waittableout=obj.name FROM '+@waitresourcedatabase+'.sys.partitions par JOIN '+@waitresourcedatabase+'.sys.objects obj ON par.OBJECT_ID = obj.OBJECT_ID
JOIN '+@waitresourcedatabase+'.sys.indexes ind ON par.OBJECT_ID = ind.OBJECT_ID AND par.index_id = ind.index_id
WHERE par.hobt_id =@waitresourceidIN'
exec
sp_executesql @sql2,@ParmDefinition,@waitresourceidIN=@waitresourceid,@waittableout=@waittable output;
--select @waittable
update #temp4
set waittablename=@waittable
where waitresourcedatabase=@waitresourcedatabase
and waitresourceid=@waitresourceid and waittablename='notdone'
SET @ParmDefinition = N' @waitresourceidIN varchar(200), @waitindexout nvarchar(500) OUTPUT'
set @sql2='SELECT @waitindexout=ind.name FROM '+@waitresourcedatabase+'.sys.partitions par JOIN '+@waitresourcedatabase+'.sys.objects obj ON par.OBJECT_ID = obj.OBJECT_ID
JOIN '+@waitresourcedatabase+'.sys.indexes ind ON par.OBJECT_ID = ind.OBJECT_ID AND par.index_id = ind.index_id
WHERE par.hobt_id =@waitresourceidIN'
exec
sp_executesql @sql2,@ParmDefinition,@waitresourceidIN=@waitresourceid,@waitindexout=@waitindex output;
if @waitindex is null
set @waitindex='notfound'
update #temp4
set waitindexname=@waitindex
where waitresourcedatabase=@waitresourcedatabase
and waitresourceid=@waitresourceid and waitindexname='page_or_object_lock_no_index_captured'
fetch next from updatecursor into @waitresourcedatabase, @waitresourceid,@waittable
end
close updatecursor
deallocate updatecursor
--select * from #temp4
----START BLOCK TO UPDATE PAGE LOCK ROWS
--declare variables
declare @pagenumber varchar(100)
declare @command varchar(500)
declare @pageobjectid varchar(100)
declare @pageindexid varchar(100)
CREATE TABLE #PageData
(
ParentObject VARCHAR(1000)NULL,
Object VARCHAR(4000)NULL,
Field VARCHAR(1000)NULL,
ObjectValue VARCHAR(4000)NULL,
)
--define cursor
declare pagecursor cursor for
select distinct waitresourceid, waitresourcedatabase from #temp4 where waitresourcetype='PAGE'
open pagecursor
fetch next from pagecursor into @pagenumber, @waitresourcedatabase
while @@FETCH_STATUS =0
begin
set @command=REPLACE(@pagenumber,'PAGE: ' ,'DBCC PAGE(')+') WITH TABLERESULTS'
set @command=REPLACE(@command,':',',')
insert into #PageData (ParentObject, Object, Field, ObjectValue)
exec (@command)
--select * from #PageData
select top 1 @pageobjectid=ObjectValue from #PageData where Field ='Metadata: ObjectId'
select top 1 @pageindexid= ObjectValue from #PageData where Field ='Metadata: IndexId'
--select @pageobjectid
update #temp4 set objectid=@pageobjectid, indexid=@pageindexid where waitresource=@pagenumber
delete from #PageData
SET @ParmDefinition = N' @waitresourceidIN varchar(200), @waittableout nvarchar(25) OUTPUT'
set @sql2='SELECT top 1 @waittableout=obj.name FROM '+@waitresourcedatabase+'.sys.partitions par JOIN '+@waitresourcedatabase+'.sys.objects obj ON par.OBJECT_ID = obj.OBJECT_ID
JOIN '+@waitresourcedatabase+'.sys.indexes ind ON par.OBJECT_ID = ind.OBJECT_ID AND par.index_id = ind.index_id
WHERE obj.object_id =@waitresourceidIN'
exec
sp_executesql @sql2,@ParmDefinition,@waitresourceidIN=@pageobjectid,@waittableout=@waittable output;
--select @waittable
update #temp4
set waittablename=@waittable
where waitresourcedatabase=@waitresourcedatabase
and waitresourceid=@pagenumber and (waittablename='notdone' OR waittablename is null)
SET @ParmDefinition = N' @waitresourceidIN varchar(200), @objin varchar(100), @waitindexout nvarchar(500) OUTPUT'
set @sql2='SELECT @waitindexout=ind.name FROM '+@waitresourcedatabase+'.sys.partitions par JOIN '+@waitresourcedatabase+'.sys.objects obj ON par.OBJECT_ID = obj.OBJECT_ID
JOIN '+@waitresourcedatabase+'.sys.indexes ind ON par.OBJECT_ID = ind.OBJECT_ID AND par.index_id = ind.index_id
WHERE ind.index_id=@waitresourceidIN and obj.object_id=@objin'
-- select @sql2
exec
sp_executesql @sql2,@ParmDefinition,@waitresourceidIN=@pageindexid,@objin=@pageobjectid,@waitindexout=@waitindex output;
if @waitindex is null
set @waitindex='notfound'
update #temp4
set waitindexname=@waitindex
where waitresourcedatabase=@waitresourcedatabase
and waitresourceid=@pagenumber and (waitindexname='page_or_object_lock_no_index_captured' or waitindexname is null)
fetch next from pagecursor into @pagenumber, @waitresourcedatabase
end
close pagecursor
deallocate pagecursor
drop table #pagedata
--select * from #temp4
insert
dba.dbo.blockingreport2013
select * from #temp4 where starttime >(select max(starttime) from dba.dbo.blockingreport2013)
drop table #temp4
delete from dba.dbo.blockingreport2013
where starttime <getdate()-60
--drop table #temp1
--drop table #temp3
end
GO