How to query for SQL text in rdl stored in reportserver catalog table

Ever make a change to your database and wonder what reports are dependant upon
it?  If you use stored procedures / views in your reports you can query the
system catalogs.  But if you are using inline SQL you have to root through
the RDL's.  The report content is an image field which means you have to
convert it to varbinary (max) then to Xml.  From there you can easily look
for your SQL.  For different versions of Reporting services use the
corresponding namespace  (found at the top of the RDL file).

with xmlnamespaces    ( 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' AS REP ) select * from (   select c.Path,      c.Name,      DataSetXML.value('@Name','varchar(MAX)') DataSourceName,      DataSetXML.value('REP:Query[1]/REP:CommandText[1]','varchar(MAX)') CommandText   from     (select ItemID,             CAST(CAST(Content AS varbinary(max)) AS xml) ReportXML      from dbo.Catalog     ) ReportXML    CROSS APPLY ReportXML.nodes('//REP:DataSet') DataSetXML (DataSetXML)   INNER JOIN dbo.Catalog c ON ReportXML.ItemID = c.ItemID ) tbl where CommandText like '%search criteria goes here...%'