x

how to find out All tables names & columns list use by SSRS Report

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

more ▼

asked Apr 13 at 07:19 PM in Default

avatar image

AlexKlap
461 12 18

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Hi there,

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.

more ▼

answered Apr 17 at 10:21 AM

avatar image

WRBI
61 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x713
x4

asked: Apr 13 at 07:19 PM

Seen: 16 times

Last Updated: Apr 17 at 10:21 AM

Copyright 2018 Redgate Software. Privacy Policy