Search SQL Server Reporting Services Report Definition

A little over a year ago, Raja and I were having a discussion about reporting services. The discussing revolved around writing the T-SQL in the report definition or creating stored procedures for each dataset and executing the stored procedure in the report definition.

However, one of the drawbacks of keeping the T-SQL in the report definition is that you cannot just simply search in syscomments to find objects that may be affected by a schema change. But searching the report definition is still possible! A report definition file is simply XML stored in the ReportServer database in the Catalog table. (It is stored using the image data type, even in SQL 2008. What is up with this, Microsoft? I thought the image data type was deprecated in favor of varbinary(max).)

So the first thing that we need to do is convert the image data to XML. You cannot explicitly convert image to XML, but you can explicitly convert image to varbinary and varbinary to XML, so that is what we will do. Next we will shred the XML using the nodes() method. This is the most effective method since a report definition can contain multiple data sets, and we want to capture all of them.

The SQL below will create a view with the report path, report name, data set name and the T-SQL for easy searching.

NOTE: The XML namespace may be different, depending on the version you are using. If this does not work for you at first, run the T-SQL from the derived table and click on the XML. The top line of the XML will contain the namespace corresponding to the version you are using.


USE [ReportServer]
GO
IF EXISTS (SELECT
 *
 FROM
 sys.views
 WHERE
 object_id = OBJECT_ID(N'[dbo].[ReportDefinition]'))
 DROP VIEW [dbo].[ReportDefinition]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ReportDefinition]
AS
WITH XMLNAMESPACES
(
'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS RES
)
SELECT
 c.Path,
 c.Name,
 DataSet.value('@Name','varchar(MAX)') DataSourceName,
 DataSet.value('RES:Query[1]/RES:CommandText[1]','varchar(MAX)') CommandText
FROM
 (SELECT
 ItemID,
 CAST(CAST(Content AS varbinary(max)) AS xml) ReportXML
 FROM
 dbo.Catalog
 WHERE
 Type = 2) ReportXML
CROSS APPLY ReportXML.nodes('RES:Report/RES:DataSets/RES:DataSet') DataSetXML (DataSet)
INNER JOIN dbo.Catalog c
 ON ReportXML.ItemID = c.ItemID
GO

Happy coding!

~Ron

Advertisements

3 Responses to “Search SQL Server Reporting Services Report Definition”


  1. 1 Tim Cleary October 8, 2010 at 1:56 pm

    This is great! very helpful. I’m surprised no one has commented about it. thanks for posting.

  2. 2 Eric March 23, 2011 at 6:34 pm

    As mentioned, the namespace will vary by version. On 2005 it is :
    http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition

    One can only assume that future versions will be of similar format, only adjusting the year.

    Thank you for this code, if only I used the correct search terms 2 hours ago…

    Found this with search “SQL View all report definitions”

  3. 3 Fabio July 7, 2014 at 9:59 am

    Great solution. Tks a lot


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





%d bloggers like this: