How to get all queries used in SSRS

Since I’m doing a massive code review resulting in a lot of query tuning recently, I needed to review all the reports on one of SSRS instances. I didn’t want to go through reports one by one, but dump all the queries and then work with them. Here’s the query I used:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd)
SELECT a.Name, CAST(a2.q.query('./text()') AS varchar(MAX)) Query FROM
(
SELECT Name,
CAST(CAST(Content AS VARBINARY(MAX)) AS XML) AS ReportXML
FROM ReportServer.dbo.Catalog
where Type = 2) a
CROSS APPLY ReportXML.nodes('/Report/DataSets/DataSet') a1(q)
CROSS APPLY a1.q.nodes('./Query/CommandText') a2(q)

It returns a list of reports with all queries used by them – just run it in your SSRS ReportServer database. Works for 2008 – 2012, can’t check it with 2005 but since the XSD of report definition does not change much from 2005 to 2008 it should work as well.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s