tim laqua dot com Thoughts and Code from Tim Laqua

Searching for keywords in all Reporting Services (SSRS) reports

During impact analysis for any changes to existing database tables, cube dimensions, cube measures, etc, it's nice to know which reports are going to horribly break before your end-users let you know about it All of the rdl content for reports uploaded to SSRS is stored in the ReportingServices database in the Content column of the Catalog table (in binary, of course) so here's what I came up with to get the list of soon to be broken reports:

SELECT [Path], ContentText
FROM
(
SELECT [Path], cast(cast([content]AS varbinary(8000)) AS varchar(8000)) AS [ContentText]
FROM [catalog] cat WITH(nolock)
WHERE [type]=2
) a
WHERE ContentText LIKE '%ColumnName%' 
	OR ContentText LIKE '%columnname%' 
	OR ContentText LIKE '%MeasureName%' 
	OR ContentText LIKE '%AttributeName%' 
	OR ContentText LIKE '%etc...%'