June 1, 2016 at 2:38 pm
We have several hundreds of tables in a database (OLTP system). Only few tables are being used by reports. We were planning to replicate only tables that are being used by reports to a different server. Is there a way to go through all queries used by SSRS reports and return tables names?
I found below query online but it just gives me reports queries. I'm not sure how to retrieve table/view names. Any help would be greatly appreciated. Thank you.
here is the query:
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
SELECT ReportName= name
,DataSetName= x.value('(@Name)[1]', 'VARCHAR(250)')
,DataSourceName= x.value('(Query/DataSourceName)[1]','VARCHAR(250)')
,CommandText= x.value('(Query/CommandText)[1]','VARCHAR(max)')
,Fields= df.value('(@Name)[1]','VARCHAR(250)')
,DataField= df.value('(DataField)[1]','VARCHAR(250)')
,DataType= df.value('(rd:TypeName)[1]','VARCHAR(250)')
--,ConnectionString = x.value('(ConnectionProperties/ConnectString)[1]','VARCHAR(250)')
FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
FROM ReportServer.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r ( x )
CROSS APPLY x.nodes('Fields/Field') f(df)
ORDER BY name
June 1, 2016 at 4:28 pm
There is no easy way to do this. Each report can be an ad hoc T-SQL statement, a stored procedure or a view. You can at least document the dependencies of the view in a straight forward manner. You can do this for stored procedures too, if they're not dynamic T-SQL under the covers. Ad hoc queries within reports have to be parsed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply