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
