Script to determine SQL Server Reporting Services parameters, path and default values

By:   |   Comments (27)   |   Related: > Reporting Services Parameters


Our company has hundreds of reports with daily, weekly, monthly, quarterly, yearly and ad-hoc schedules. Is there a way to identify all the reports with their path, parameters and default values as well as if the parameters are hidden or visible when executing the reports?  How do I check this information programmatically without manually reviewing each report?  Is this information stored in system tables or DMV's that I can query?


SQL Server Reporting Services stores a lot of metadata about the reports in a rich set of tables in the ReportServer database. For this solution, we will take advantage of the dbo.Catalog table where the report characteristics are stored in a column named Content.  The data is in XML format, but it is stored as image data type.  Luckily, SQL Server 2005 offers a rich set of XML functions and we will be using those to generate the data rather than having to review the reports manually.

Here is the script to generate the data with all the report names, parameters and its default values:
--Find all the reports, and thier parameters and thier default values
'' AS rd --ReportDefinition
   x.value ('@Name''VARCHAR(100)'AS ReportParameterName
   x.value ('DataType[1]''VARCHAR(100)'AS DataType
   x.value ('AllowBlank[1]''VARCHAR(50)'AS AllowBlank
   x.value ('Prompt[1]''VARCHAR(100)'AS Prompt
   x.value ('Hidden[1]''VARCHAR(100)'AS Hidden
   x.value ('data(DefaultValue/Values/Value)[1]''VARCHAR(100)'AS Value
           CAST(CAST(content AS VARBINARY(MAX)) AS XMLAS ReportXML 
   FROM ReportServer.dbo.Catalog 
CROSS APPLY ReportXML.nodes('/Report/ReportParameters/ReportParameter'R(x)
--WHERE NAME = 'Sales_Report'
--Use the where clause above to look for a specific report

The result from the script above will look something like this:

ReportingServices Parameters


Here are few more screenshots on how the parameters might look on the DailyBlogReport:

ReportingServices Parameters2

ReportingServices Parameters3


The Report Definition Language (RDL) for these parameters is outlined below:

ReportingServices Parameters4

Next Steps
  • Next time you need to determine report parameters, consider the script from this tip as an option to drill into that information.
  • This tip also sheds some light into where the report metadata is stored and how to work with the data.  As you face similar challenges, consider this script as a baseline to begin to address your needs.
  • Take a look at more Reporting Services and XML tips on MSSQLTips.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Sankar Reddy Sankar Reddy is a Database Engineer/DBA and SQL Server MVP. He has been working with SQL Server since 2003 in a variety of roles.

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

Thursday, August 7, 2014 - 7:13:57 AM - Erlon Rodrigues Cruz Back To Top (34037)

Hi Sankar,


Nice tip. My problem is that I don't have access to the database SQL console. Is it possible to retrive that information using the execution or the management endpoint??


mngm_endpoint = "http://<server_ip>/ReportServer/ReportService2010.asmx?wsdl"

exec_endpoint = "http://<server_ip>/ReportServer/ReportExecution2005.asmx?wsdl"

Thursday, September 19, 2013 - 1:18:59 PM - Steve Back To Top (26869)

Hmmm... noticed that some of my reports have the 2010 schema path in the rdl (developed in VS 2012), so I ran the query with that changed and got back 43 reports.  Looks like I have a mix of schema paths...I vaguely recall reading something somewhere about the schema updating only if you use features unique to the later schema... so I'll amend my previous comment form "this needs work" to "be careful"!

Thursday, September 19, 2013 - 1:11:07 PM - Steve Back To Top (26868)

This needs work... ran it on a 2008 R2 instance, with the schema URL updated, and it only listed 14 of my reports.  There are 50+ reports, all of which have parameters. 

Wednesday, June 19, 2013 - 9:54:22 AM - dondadda Back To Top (25486)

I'm using SSRS again, and this was brilliant. I have tried to find RSSCRIPTER but unable to locate a valid source, this has helped a lot.

Thursday, June 6, 2013 - 6:46:54 AM - Wayne Back To Top (25319)


I am looking for something similar but to be able to access and change the parameters of a linked report, is this possible?

Wednesday, June 5, 2013 - 3:26:20 PM - xtrout Back To Top (25315)

I am looking for something similar for listing the paramter used in a subscription. We have a report that has 60+ subscritptions based on the change of one parameter (application name). Unfortunately the default screen displays the same info with each subsctiption so there is no way to differentiate the application in the list. Do you have any code that may help me there? I cannot seem to wrap my brain around parsing out the XML parameter values.

Tuesday, March 12, 2013 - 7:58:14 AM - suchana Back To Top (22733)


Tuesday, March 12, 2013 - 7:56:33 AM - suchana Back To Top (22732)



 New to SSRS i need to generate a report using XML.



Columns :EmpID

              Emp Name,Salary,Address,

Information is Stored In DB Under EMPloyee table with EMPXML column aginast EMPID(<

FIELDS><VALUES><EmpName>XYZ</Empname><Salary>10000</Salary><Address>New Jersy</Address></VALUES></FIELDS>)


Need To generate Report based on Emp Name(parametr)

Report should be

Emp Name                          Salary                      Address

Xyz                                       20k                            NJ,USA






Tuesday, February 5, 2013 - 5:52:13 PM - Gayle Back To Top (21940)

Great Post!


Thank you

Thursday, April 5, 2012 - 5:36:18 AM - Mayur Back To Top (16785)


Good post.

I have one problem regarding getting value of multi-value parameter of SSRS report.

In ReportServer database, how i fetch the value of multi-value parameter dataset, do you know any query or sp to fetch multi-value parameter dataset name used in SSRS.


Actually in my .net application i have to bind parameter which i get from ReportServer. if parameter type is multi-value then i have to show dropdownlist for that parameter, but what are the items in that dropdown. i want to bind that dropdownlist. so please send me query or sp which is used to fetch multi-value parameter value

Thanks in advance


Wednesday, April 4, 2012 - 1:31:02 PM - Kris Back To Top (16776)

This is awesome! I was just working on writing something like this.  You rock, Sankar.  

Good catch Chadwicksmith with the 2008 stuff.     =D

Tuesday, July 27, 2010 - 1:28:57 AM - SankarReddy Back To Top (5896)

Just replace the definition like below and you should be fine. --SQL Server 2005 DEFAULT '', --SQL Server 2008 DEFAULT '',


Tuesday, July 27, 2010 - 12:53:24 AM - [email protected] Back To Top (5895)

for me also same problem. when I executed the sql in my local server(sql 2008) did not find a single record. Could you suggest.

Wednesday, March 31, 2010 - 9:53:28 AM - ChadwickSmith Back To Top (5152)

I figured it out.  If you're using SQL 2008, you have to replace this line:

      DEFAULT '',

with this:

      DEFAULT '',


Once you change that it works fine!

Wednesday, March 31, 2010 - 9:24:42 AM - ChadwickSmith Back To Top (5151)
I have the same problem, except It doesn't return any rows for me.  There are 224 reports in dbo.Catalog with TYPE = 2, but it doesn't return any rows when I run your query.  We are running SQL 2008 with SharePoint Integrated Mode.

Friday, February 12, 2010 - 2:29:36 PM - rduclos Back To Top (4892)
I have done something similar in the past. I use it to display a section on the first page of all my reports to show what parameters and values were used or changed from defaults on the report.

Thursday, November 12, 2009 - 1:19:22 PM - SankarReddy Back To Top (4425)


I heard back from MSFT and thier response is same as mine earlier, which is "don't do this" via T-SQL. You can do this via Reporting Services API for which I gave the link above. Even if you try with T-SQL and note that if something goes wrong (which might happen, if you apply a hotfix, service pack, version etc...) your instance will NOT be supported by MSFT. Thats a hefty price tag for someting so trivial.


Saturday, October 31, 2009 - 2:13:44 PM - kirthikajanani.p Back To Top (4350)

 Thanks a lot for looking into this. Really appreciate the help. 

Saturday, October 31, 2009 - 1:39:10 PM - SankarReddy Back To Top (4349)


MSFT recommends NOT to change the ReportServer database schema. Upgrading ReportServer Database

After your question I searched a little bit more and found a link from MSFT that you can do updates to ReportDefinition using the .Net framework using VB/C#. I am NOT sure if it is recommended doing this via TSQL. Let me find that out with some experts and will get back to you.

Tutorial: Updating Reports Using Classes Generated from the RDL Schema



Saturday, October 31, 2009 - 1:07:21 PM - kirthikajanani.p Back To Top (4348)

 Hello Sankar,


Thank you for the reponse. I totally agree with you on this aspect but updating huge number of reports manually is near impossible.

Have you faced problems in such an update or does MSFT advice against this ?




Friday, October 30, 2009 - 6:09:43 PM - SankarReddy Back To Top (4346)
[quote user="kirthikajanani.p"] I am also trying to update one parameter  in all these xmls  at one go using this script. [/quote]


I am NOT sure if it is a wise decision to do. If you ask me, I wouldn't advice to do that way. MSFT recommends NOT to touch these system tables or change the structure of the tables of the ReportServer database. I understand its a pain to change all reports and deploy them again but that is the correct and *safest* way to update the reports. Updating the XML to make changes to the report is asking for trouble.


Friday, October 30, 2009 - 5:54:24 PM - kirthikajanani.p Back To Top (4345)


 This script was very useful. I am also trying to update one parameter  in all these xmls  at one go using this script.


Though am able to select the xml by convering image to xml and varbinary, I am having trouble contructing an update statement because it has to be stored not as xml but as image in the database.


Can someone please help by posting any update they have done to the  Catalog table using update statements ?


This will come in handy especially when you dont want to go change things in the deployed report but change parameters for these directly at the database level.




Saturday, October 17, 2009 - 3:12:08 PM - SankarReddy Back To Top (4222)
Jerry, That's really weird. I don't have the configuration you have handy with me, is it possible for you to run the below steps if you don't mind. 1. Grab the XML from the dbo.Catalog and use it as an xml variable. 2. Modify the tip query to shred from the above XML variable in SQL Server 2005 instance. Or if you can send me a sample xml where you had the issue, I will be glad to take a look for you. Send me the xml to replace * with period

Friday, October 16, 2009 - 3:36:05 PM - jerryhung Back To Top (4212)

Weird, I ran the query and it only returned like 2 reports (5 parameters in total), out of the 174 reports (and many of them have default parameters)

I did look at a few of report's XML in .dbo.Catalog to see the <ReportParameters> filled with correct data as well, so it is quite odd

This is on SQL 2008 DB and SSRS, x64

Thursday, September 17, 2009 - 1:23:34 PM - JohnMarsing Back To Top (4052)

Thanks for the tip, this will be great for documenation purposes.  I don't have SSRS running on my laptop but I am using a win forms application that uses the report viewier controller.  Do you have any ideas how you could run your scrpit against my .rdlc files that I have created?

 Thanks John

Thursday, September 17, 2009 - 3:22:50 AM - SankarReddy Back To Top (4051)
Glad its useful to you.

Thursday, September 17, 2009 - 2:41:46 AM - NTHustler Back To Top (4050)
Nice tip, thanx. Actuallly need this today to do some pre deployment checking of big number of reports.

get free sql tips
agree to terms