Hi i have 600 SSRS Reports deployed into production. how can i get meta data information of reports. like which reports used which all table.if any reports used more than 1 table i am trying to get all table use by reports.
will it be possible to get such info. Please help
SQL SERVER 2012
asked Apr 13 at 07:19 PM in Default
I wrote something like this before, however, I don't have access to the code at this time and I don't have access SSMS where I am, but this answer should guide you even though its a bit sketchy!
Assuming that you've deployed the reports, they'll be in the ReportServer database under dbo.Catalog, in here there is an XML field (possibly called ContentXML) that contains all the data about a report. You can parse the XML in this column to get all the data that you want. It will include stored procedure names that you can then look at to see what tables they use.
You can use the CommandType and CommandText to retrieve the SQL that used directly in the Reports.
I extract all the above data into it's own table overnight and then I can query that without parsing XML each time.
Hope that helps.
answered Apr 17 at 10:21 AM