List of SSRS Reports for a string Containing in its Name , Dataset, Path , DataSourceName

            On Production server, it is difficult to get the List of SSRS Report having certain DataSourceName, located at certain path or with certain Dataset or ReportName. Because when deploying the rdl files on production server through browser, it doesn't store exactly the same as we have at our local machine. In fact it stores in Catalog, Subscriptions and other tables in ReportServer database in XML format.

           So parsing the XML content from these tables can help to find the list of such reports.

Below is the script to pull the reports using various filters.

--;WITH XMLNAMESPACES ('http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd, 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition' AS cl, DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition')
;WITH XMLNAMESPACES ('http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd, DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition')
,CTE
AS
(
SELECT Name AS ReportName, Path AS ReportPath ,
  CONVERT(XML, CONVERT(varbinary(MAX),Content)) Content
FROM Catalog WITH (NOLOCK)
WHERE Name LIKE '%MISReport%'  -- 1. Filter for Report Name
)
--SELECT * FROM CTE
SELECT ReportName
  ,ReportPath
  ,b.value('DataSources[1]/DataSource[1]/DataSourceReference[1]','varchar(100)') AS DataSourceName 
  ,d.value('@Name','varchar(100)') AS DatasetName 
  ,s.value('CommandText[1]','varchar(max)') AS CommandText 
FROM CTE
  CROSS APPLY Content.nodes('Report') AS a(b)
  CROSS APPLY b.nodes('DataSets/DataSet') AS c(d)
  CROSS APPLY d.nodes('Query') AS r(s)
--WHERE d.value('@Name','varchar(100)') LIKE '%Activation%'  -- 2. Filter for Dataset Name
--WHERE ReportPath LIKE '%/LiveReports/Common/Activations/%'  -- 3. Filter for Path
--WHERE b.value('DataSources[1]/DataSource[1]/DataSourceReference[1]','varchar(100)')  -- 4. Filter for DataSourceName

    
 

           In above script we have shown two XMLNAMESPACES, use any one whichever is applicable as per your machine configuration.
Similarly Uncomment any of the filter you desired listed as below :
1. Filter for Report Name
2. Filter for Dataset Name
3. Filter for Path
4. Filter for DataSourceName 

---------------------------------------------------------------------------------------------

 Below is the script to pull the List of Reports which are subscribed to the given recipient.

;WITH CTE
AS
(
SELECT C.ItemID, C.Path, C.Name, 
  CONVERT(XML,S.ExtensionSettings) AS ExtensionSettingsXML ,
  CONVERT(XML, CONVERT(varbinary(MAX),Content)) Content ,
  S.LastStatus ,
  S.LastRunTime
FROM CATALOG C 
  INNER JOIN Subscriptions S ON C.ItemID = S.Report_OID
WHERE C.Name LIKE '%MISReports%'
)
SELECT ItemID, Path, Name, Content
FROM CTE
  CROSS APPLY ExtensionSettingsXML.nodes('ParameterValues/ParameterValue') a(b) 
WHERE b.value('Name[1]','varchar(50)') IN ('TO','CC','BCC')
  AND b.value('Value[1]','varchar(1000)') LIKE '%govind.badkur@gmail.com%'
    
 

Reference: Govind Badkur(http://sqlserver20.blogspot.com)