In the past I have published a few queries that allows you to query SQL Server Reporting Services database – to retrieve specific information about various reports deployed in the Reporting Server Instance.
Recently, I got a question in my ASK forum who wanted to identify all the reports that use a specific column. I thought of writing a blog post about it so that it can be added to the SSRS query series and help other people with similar problems.
The RDL file that gets generated when you design a report, is an XML document. When you deploy the report on Reporting Server, the RDL file (which is an XML document) gets inserted into the Report Server database (named ReportServer by default). You can query this database to obtain the XML content of all the reports and then use XQuery to read specific information about each report.
The following query demonstrates how to read all the columns used by all the reports in the given SQL Server Reporting Server instance and search for a specific column. The query given below returns the report name and data source name of all reports that references a specified column name – which is passed as a parameter.
DECLARE @FieldToSearch VARCHAR(100)
SELECT @FieldToSearch = 'PatientNumber'
;WITH XMLNAMESPACES (
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
d.value('@Name', 'VARCHAR(50)') AS DataSetName,
df.value('@Name', 'VARCHAR(50)') AS ReportFieldName,
df.value('DataField', 'VARCHAR(50)') AS DataFieldName
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
WHERE Type = 2
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r(d)
CROSS APPLY d.nodes('Fields/Field') f(df)
WHERE df.exist('DataField[ . = sql:variable("@FieldToSearch")]') = 1
Name DataSetName ReportFieldName DataFieldName
------------ ------------ --------------- ---------------
Patient List DSPatients PatientID PatientNumber
AR Details DSPatientsAR PatientNumber PatientNumber
The code given above is tested with SSRS 2005 SP2. It is expected to work on other versions, but I have not tested. If you find a problem running this query on other SSRS versions, let me know.
I would recommend you read the following posts as well:
- How to find all stored procedures used by Report Server?
- How to query report server to find out the data source used by one or more reports?