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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply