Search SSRS Datasets in SQL

The XML for SQL Server Reporting Services Reports are stored in the ReportServer Database of the server. Its possible to pull the XML out and then use the contents to find which reports are using which tables etc. This can be very handy if you there is a change to a schema where logic in reports may need to be updated but you dont know which reports need to be looked at.

The below will place the XML into a table so you can use it to filter what you are after.

CREATE TABLE [dbo].[ReportContents](
 [CatalogItemID] [uniqueidentifier] NOT NULL,
 [Type] [int] NOT NULL,
 [Path] [nvarchar](425) NOT NULL,
 [Name] [nvarchar](425) NOT NULL,
 [ItemContent] [xml] NULL
) ON [PRIMARY]

INSERT INTO ReportContents
 (CatalogItemID, [Type], [Path], [Name], ItemContent)


SELECT
 ItemID
 , [Type]
 , [Path]
 , [Name]
 , cast(CONVERT(VARCHAR(MAX), CONVERT (VARBINARY(MAX), [Content])) as xml) As [ItemContent]
FROM ReportServer.dbo.Catalog
WHERE [Type] <> 1     --Folder
   AND [Type] <> 3 --Image

The below queries the table you created, adjusting the value of the @SQLToCheckFor variable will adjust which text to look for within the SQL Datasets of the SSRS XML. Note this is set up for SQL2008, from memory its just a change of the http address from 2008 to 2005 etc.

--Check all the reports to find a bit of SQL

Declare @SQLToCheckFor varchar(500) = 'FACTMemberBalance'--'dPlanClassification' --Enter your bit of SQL here

select MydataSets.CatalogItemID, MydataSets.ReportName , MyDataSets.DataSetName,
MydataSets.CommandText, MydataSets.ReportPath
from
 (
  select
   CatalogItemID,
   [Name] As ReportName,
   [path] as ReportPath
   ,nref.value('@Name', 'nvarchar(255)' ) As DataSetName
   ,nref.query('.') As DataSetXML
   ,nref.value('declare namespace p1="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";
     (./p1:Query[1]/p1:CommandText)[1]', 'varchar(max)') As CommandText
   ,nref.value('declare namespace p3="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";
      declare namespace p1="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";
      declare default element namespace "http://schemas.microsoft.com/AnalysisServices/QueryDefinition";
     (./p1:Query/p3:MdxQuery/QueryDefinition/QuerySpecification/From)[1]', 'varchar(max)') As Cube   
  from ReportContents
  cross apply ItemContent.nodes('declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";
            declare namespace p1="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";
  //DataSet') as R(nref)
--A filter can be added here to limit what reports you are looking through
 )MyDataSets
where CommandText like '%' + @SQLToCheckFor + '%'