Find Tables, Columns or Stored Procedures used in a SQL Server Reporting Services Report

By:   |   Comments (25)   |   Related: > Reporting Services Administration


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?


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: 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: 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.

catalog table

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.

catalog content

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.

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.

XML Results

XML Only

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".

, '' AS ReportDefinition )
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 ReportServer.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') xmltable ( xmlcolumn )
xmlcolumn.value('(Query/CommandText)[1]','VARCHAR(250)') LIKE '%ProductCategoryName%'

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 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 - ). For SSRS 2014, is used whereas SQL 2008R2 uses 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.

, '' 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 )
xmlcolumn.value('(Query/CommandText)[1]','VARCHAR(250)') LIKE '%ProductCategoryName%'

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.

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 ReportServer.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
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.

XML with Detail

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).

Final Query Results

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.

Authors Query


In this tip we explored an unsupported method of querying SSRS report definitions. This method does the following:

  1. Extracts the report definition from the Catalog table
  2. Converts the report definition from binary to a XML data type
  3. Uses cross apply and the XML node method to extract the appropriate part of the XML from the definition and
  4. 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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Scott Murray 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

Comments For This Article

Monday, November 21, 2022 - 9:55:52 PM - Sean Back To Top (90706)
Hi, xml namespaces can be ignored, try the following:

SELECT ContentXml = CAST(Content AS XML),
FROM dbo.Catalog
) Catalog
SELECT DatasetName = X.rec.value('(@Name)[1]', 'VARCHAR(250)')
FROM ContentXml.nodes('//*:DataSet') AS X(rec) -- (*: = "Ignore xml namespaces")
) nodes
WHERE nodes.DatasetName IS NOT NULL

Tuesday, June 21, 2022 - 2:19:07 PM - sree Back To Top (90178)
1) Use "OUTER APPLY" in place of "CROSS APPLY", gives report names and nulls for dataset, data source, command text names, in case none (very rare case)

2) Use this URL for latest SSRS

Wednesday, January 12, 2022 - 2:18:44 PM - Scott Murray Back To Top (89653)
I am not aware of a way to get the Power BI equivalent at this time.

Wednesday, January 12, 2022 - 1:02:54 PM - winceeS Back To Top (89652)
this helped me a lot, thank you! I wonder if there is something like this to get for Power BI Reports (c.type = 13)? I noticed that Type 13 only returns null. Is there a way to have a query like this to search for a word used in a power BI report in the power BI report server catalog?

Tuesday, December 22, 2020 - 8:24:21 AM - Scott Murray Back To Top (87941)
You would need to parse the actual select statement. There are a few tools out there that do that. I am not sure about their validity. Something like: could help.

Tuesday, December 22, 2020 - 2:37:32 AM - Urbi Ghosh Back To Top (87939)
I want to break down the CommandText to get all the tables, respective field names corresponding to the tables in the end result table. I am unable to do that . Please guide!!

Wednesday, September 9, 2020 - 11:18:08 AM - Scott Murray Back To Top (86445)
the search criteria could easily include table names which appear in the dataset query,

Wednesday, September 9, 2020 - 10:24:43 AM - Noreen Geehan Back To Top (86444)
This is great, thank you.
I'm able to search values in the select portion of the datasets but not the tables names - from portion. Can you please help me understand how to search by table name.

Tuesday, March 24, 2020 - 10:27:30 AM - Jen Back To Top (85176)

This was exactly what i was looking for!  Thank you so much for posting.

Tuesday, July 23, 2019 - 5:27:40 PM - CuriousKid Back To Top (81845)

Great post Scott. It is really helpful.

I am working on a task in which i am trying to get all the dependent objects(Table, Stored Procedures, Function, Views) for a report. The report which have stored procedure are quiet direct where i can use sp_depend or similar dmv to get the dependencies of the report stored procedure but in case of the report with an inline text query I am also extracting all the inline queries of Rdl through XML parsing and need to get dependent objects of the inline query.

I was able to Parse the XML and extract inline query as mentioned above for all my reports but i am stuck where i have to get dependent objects of the inline text query.

Is this even possible? Is there any Query/Method/Tool to extract Dependent Objects of the Inline Query of an SSRS report?

Here is the Question i posted on stackoverflow 2 moonths ago but not a quality response yet:

Wednesday, May 1, 2019 - 11:46:43 AM - Scott Murray Back To Top (79896)

I would check your SSRS version.

Wednesday, May 1, 2019 - 10:55:10 AM - Jason Y Back To Top (79894)

Getting error: Msg 9420, Level 16, State 1, Line 2

XML parsing: line 1, character 3, illegal xml character

Wednesday, November 14, 2018 - 9:42:42 AM - Scott Brian Murray Back To Top (78241)


I would take a look at the Catalog.Type field.  I believe type=5 would be datasets.

Tuesday, November 13, 2018 - 8:49:37 PM - Jim Back To Top (78233)



Thanks very much for this.  Our reports all use shared datasets which don't have embedded 'Select' or Exec statements, but that call a stored procedure.  However, the query you provided returns null in the command text column; how  can I get the names of the actual SPs in use?  The SPs that are currently in use on our reports are only a small subset of the total SPs on the server (multiple versions, etc.) and we'd like to identify those that are in use and remove / archive the rest.

Tuesday, June 12, 2018 - 5:45:54 AM - Fehmi Back To Top (76192)


Thank you soooo much. 

Friday, March 30, 2018 - 9:37:30 AM - Michael Glasser Back To Top (75563)

 This code has been great for deciphering the SSRS XML, but it looks for specific tables or fields via a specific string.  How would you suggest that I parse the tables used by a query into a separate table?  In other words, identify all the tables for a report rather than looking for a specific table.


Thanks, Mike


Tuesday, November 28, 2017 - 2:35:49 PM - Scott Murray Back To Top (73352)


Shawn, I would check the version you are using.  This item was tested primarily on SQL 2014

Tuesday, November 28, 2017 - 1:28:43 PM - Shawn Back To Top (73350)

I am receiving the following message: "XML parsing: line 1975, character 10, illegal xml character".

Friday, August 14, 2015 - 11:54:24 AM - Scott Back To Top (38450)

I only have SQL 2014 and 2016 CTP... I thought the prior versions should work.  Have you tried looking at the text (do not convert to XML) to see what is on line 1679.

Friday, August 14, 2015 - 11:19:12 AM - Ronnie Jones Back To Top (38449)

I'm running SQL 2012 sp2.

Friday, August 14, 2015 - 10:56:16 AM - Scott Back To Top (38448)

What version of SQL are you running?

Friday, August 14, 2015 - 10:25:49 AM - Ronnie Jones Back To Top (38447)

Hi Scott - I can see this being a great tool but I'm encountering an error when trying to run the first part of the script that I'm hoping you can help me to resolve. The error is:

Msg 9420, Level 16, State 1, Line 1

XML parsing: line 1679, character 10, illegal xml character


Please let me know what you think.





Wednesday, August 5, 2015 - 4:13:27 PM - Brian Back To Top (38386)

Great post!! Nicely done sir!

Wednesday, August 5, 2015 - 8:19:53 AM - jonmcrawford Back To Top (38376)

My first thought was - rename the column and see what breaks.....but this solution is good too!

Wednesday, August 5, 2015 - 2:28:37 AM - Manoj Back To Top (38373)


get free sql tips
agree to terms