By: Scott Murray | Comments (25) | Related: > Reporting Services Administration
Problem
I need to find all the SQL Server Reporting Services (SSRS) reports that use a particular table, column, or stored procedure? Is there an easy way to accomplish this task?
Solution
SSRS, XML, and EASY are most likely not voiced in the same sentence by an SSRS developer or report designer. However, the SSRS designer does not need to fear the XML reaper. The ReportServer database, which houses all the details concerning an SSRS instance, contains several tables which contain all the details about a report. Specifically, the Catalog table is the storage location for the main report definition. However, the data is stored in binary format as XML data. We will use this XML data to extract out the details needed. Please note that all the querying we show in this tip are "not documented and unsupported" by Microsoft. Also, it could easily stop working in future versions. Even so, it is a fun exercise to combine SSRS with querying XML.
We will use the Adventure Works databases as the basis for our SSRS report querying. The 2014 versions of the regular and data warehouse databases are available on Codeplex: https://msftdbprodsamples.codeplex.com/releases/view/125550. Once you download and install the SQL Server databases, you can use SQL Server Data Tools for Business Intelligence (SSDT-BI) for Visual Studio 2013 to develop reports. You can download SSDT-BI from: http://www.microsoft.com/en-us/download/details.aspx?id=42313. Finally we will use SQL Server Management Studio to query the Catalog table.
Querying the SQL Server Reporting Services Catalog Table
The catalog table contains many fields, as shown below, but we only need to query just a few of fields to get the information we need. Specifically, we would like the Name field to get the name of the report, the Path field to retrieve the folder location of the report, and finally the Content field which contains the actual full report definition. Also, we need to use the Type field in the criteria; the type field defines the object type, such as report or folder. For this example, we will stick with reports which are Type=2.
Doing a quick query against the Catalog table, we can see, as shown next, that the path and name fields are quite simple to follow. However, notice the Content field is in binary format. This field contains the full report definition, but we cannot query it directly. We first need to convert the field from binary to text.
The first part of our query process is to pull the data from the needed fields. As shown in the code section below, we must convert the Content field to VarBinary. This conversion pulls the data out of the VarBinary format; we must then convert the text to XML format which is the outer Convert statement. The outer most Convert notates that we want the result to be returned in XML format.
SELECT
C.Name
,c.Path
,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
,C.Content
FROM ReportServer.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
Running this query, we see in the first illustration below that the reportXML column shows our report definition in XML form. Management Studio allows us to click on a row in the XML column to see the full XML detail; this detail is shown in the second screen print.
Now we have the XML version of the report definition, we can parse out the parts of the XML that we need to solve our problem. This second step requires using some of the SQL XML functions to get the pieces we need. The process actually requires several bits of code to complete the query.
The complete query is listed below. Here we are querying to find any data that is like "ProductCategoryName".
WITH XMLNAMESPACES
( DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'
, 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS ReportDefinition )
SELECT
CATDATA.Name AS ReportName
,CATDATA.Path AS ReportPathLocation
,xmlcolumn.value('(@Name)[1]', 'VARCHAR(250)') AS DataSetName
,xmlcolumn.value('(Query/DataSourceName)[1]','VARCHAR(250)') AS DataSoureName
,xmlcolumn.value('(Query/CommandText)[1]','VARCHAR(2500)') AS CommandText
FROM (
SELECT C.Name
,c.Path
,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
FROM ReportServer.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
) CATDATA
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') xmltable ( xmlcolumn )
WHERE
xmlcolumn.value('(Query/CommandText)[1]','VARCHAR(250)') LIKE '%ProductCategoryName%'
ORDER BY CATDATA.Name
In the followig sections, I will break up the code and describe each section to get us to the final end result.
The first four lines of code, shown below, specify the XML schema definition to be used for the SSRS report details. It maps all the text in the Content column to the actual SSRS XML schema for the report file. These lines point the query to go to http://schemas.microsoft.com/sqlserver/reporting/ to get the schema mapping. Line 3 is actually dependent on the version of SSRS that is being used (see this link for the versions - http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition/ ). For SSRS 2014, http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition is used whereas SQL 2008R2 uses http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition. Notice the year is different, but the year does NOT necessarily match the server version. Also, if you have upgraded reports from prior versions, it is very likely the older schema definition is still used, for backward compatibility.
WITH XMLNAMESPACES
( DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'
, 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS ReportDefinition )
Now we are going to jump to the last 4 lines of the query next as they are the basis for the middle section of the SQL code. This part of the code, shown below, starts with a cross apply; the cross apply produces a row for each matching row in the sets listed on the left and right side of the cross apply. It works very much like an inner join except that is it often used with table value expressions or functions. You can see many more details about cross apply (and outer apply) in Arshad Ali's Cross and Outer Apply tip.
Back to the below code, the cross apply subsequently uses a XML method called nodes to retrieve the XML node that pertains to Report/Datasets/Dataset level. In essence, we are telling the query to open the XML data column, drill to the XML node that is at the Report/Datasets/Dataset level, and return that data. Finally, the "xmltable ( xmlcolumn )" part of the first line tells the query to return the retrieved XML result set to an aliased table called xmltable and specifically a column called xmlcolumn. I am sure you know what the "where" key word is on the second line; likewise, the fourth line is a simple order by clause. The third line is the criteria for the where clause. This line matches one of the lines in the Select portion of the query, which will be explained shortly. For now just know that line looks for any reports that use the ProductCategoryName column.
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') xmltable ( xmlcolumn )
WHERE
xmlcolumn.value('(Query/CommandText)[1]','VARCHAR(250)') LIKE '%ProductCategoryName%'
ORDER BY CATDATA.Name
The middle set of code, displayed below, does much work and also directs what fields are to be returned in the result set. We have already discussed the section which starts with "FROM" and ends with the CROSS APPLY; the sub query and cross apply act as the basis for generating the actual columns in the query result set. Moving up to the first 6 lines displayed below; these lines designate the actual columns to be returned.
The CATDATA.Name AS ReportName and CATDATA.Path AS ReportPathLocation lines just retrieve the Report Name and Report Path from the embedded sub query against the Report Server Catalog table.
The next 3 lines, all beginning with xmlcolumn.value, are more complicated. These lines use the XML value method to "extract or shred" the XML that is returned by the sub query and cross apply and convert it to a varchar data type. The xmlcolumn.value('(@Name)[1]', 'VARCHAR(250)') AS DataSetName line actually requests the Name attribute from the Dataset element. The xmlcolumn.value('(Query/DataSourceName)[1]','VARCHAR(250)') AS DataSoureName and ,xmlcolumn.value('(Query/CommandText)[1]','VARCHAR(2500)') AS CommandText lines act in a slightly different manner; instead of returning the attribute (which is more a description of an element); they actually return the values for the requested element. In our example, the element data is for the DataSourceName and CommandText fields. You will notice the "[1]" after the element or attribute names; this item is called a singleton and is required to return data at varying levels. Finally, the lines convert the returned data, in all three cases to varchar data types. Also note that the CommandText column is what is used in the "where" clause.
SELECT
CATDATA.Name AS ReportName
,CATDATA.Path AS ReportPathLocation
,xmlcolumn.value('(@Name)[1]', 'VARCHAR(250)') AS DataSetName
,xmlcolumn.value('(Query/DataSourceName)[1]','VARCHAR(250)') AS DataSoureName
,xmlcolumn.value('(Query/CommandText)[1]','VARCHAR(2500)') AS CommandText
FROM (
SELECT C.Name
,c.Path
,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
FROM ReportServer.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
) CATDATA
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') xmltable ( xmlcolumn )
It probably would be helpful to again look at the XML generated for the report. In the below screen print, the three fields we are extracting from the XML are noted by a number.
Combining all the pieces of the query together, we now can finally run the query. The results, illustrated below, show we have two reports that use the ProductCategoryName column. Again, if we have older reports that were converted from prior version, we would need to adjust our query to also use the 2008 schema (or even prior to that if reports were converted from SQL 2005).
Using a similar approach, we could actually explore the various authors of each report. In the below screen print, use the Report/Author element to list each reports' author (notice the AdventureWorks reports have a Microsoft author and some have no author at all). One other item to note is that the cross apply is actually returning all the XML data as the root node level "/" is used.
Conclusion
In this tip we explored an unsupported method of querying SSRS report definitions. This method does the following:
- Extracts the report definition from the Catalog table
- Converts the report definition from binary to a XML data type
- Uses cross apply and the XML node method to extract the appropriate part of the XML from the definition and
- Finally uses the XML value method to pull out the element values or attributes which should be returned in the result set.
This functionality allows us to see, for instance, who the report author is and which reports use a specific table, column, or stored procedure.
Next Steps
- Importing and Processing Data from XML Files - https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/
- Importing XML documents using SQL Server Integration Services - https://www.mssqltips.com/sqlservertip/3141/importing-xml-documents-using-sql-server-integration-services/
- Basic SQL Server XML Query - https://www.mssqltips.com/sqlservertip/2889/basic-sql-server-xml-querying/
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
