A little while ago I wrote a piece about finding database objects that rely on other objects that no longer exist - OK, I have my database ready, now what's missing? . This is linked to that sort of process. Many SQL Server installations are associated in some way with a Reporting Services installation, it's a very logical way to distribute your database contents to system users so they can work effectively.

Databases, in many cases, are very dynamic and have work going on to make them more efficient or to carry out different roles and processes. This means that some objects come and go and if you happen to have a report that was written to consume data from a specific stored procedure and the procedure gets dropped in favour of a better method or even if the name stays but the dataset changes then your report can suddenly start throwing errors instead of making an accountant happy.

Unless you have incredible documentation on your deployed reports and the TSQL they use, whether to reference a stored procedure or a view etc., then you can easily make a breaking change.

One way to avoid an angry accountant is query the Report Server database. Now, the rdl files that you upload to your Report Server are stored in the database as a binary data type so there is some shenanigans to getting in there and seeing what's going on.

First of all lets use a temp table so we don't do anything silly to the live Catalog table
IF OBJECT_ID('tempdb..#ReportContents') >0
    DROP TABLE#ReportContents
GO
-- collect required rows
SELECT
    [PATH],
    NAME,
    CONVERT(XML,'')AS xml_content,
    CONVERT(VARCHAR(MAX),REPLACE(CONVERT(VARBINARY(MAX),c.Content),'',
                                  '')) AS [Content]
INTO
    #ReportContents
FROM
    dbo.[Catalog] as C
WHERE
    [C].[Type]=2
    ANDLEFT(NAME,1) !='{'

-- create XML type content
UPDATE
    [#ReportContents]
SET
    [xml_content]=CONVERT(XML,content)

So this leaves us with human-readable report definitions, albeit in an XML format...  
-- prove data is there
SELECT
    [rc].Name,
    [rc].[xml_content],
    [rc].[Content]
FROM
    [#ReportContents] AS rc

Great, but if we have a busy Report Server we might have hundreds of reports and I may only want to find the name of a particular procedure so I can amend the 2 or 3 reports that will be affected. This is where XQuery is your friend. Now my XQuery isn't strong but I rolled up my sleeves and made a start. After a few hours of stress I called in my friend and fellow moderator at ASK SSC, Kev Riley (Twitter | Forum) who is much more familiar with XML and after a few moments of his input we had a script as below(*):

;WITH
XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'
AS ns1),
ReportDefinition AS (
SELECT name, path,
rc.xml_content.query('//ns1:CommandText').value('(/ns1:CommandText)[1]','varchar(max)')
AS CommandText,
rc.xml_content.query('//ns1:CommandType').value('(/ns1:CommandType)[1]','varchar(max)')
AS CommandType
FROM
       #ReportContents rc)
      
SELECT
    [Name] AS [ReportName] ,
    [Path] AS [ReportPath] ,
    CommandText ,
    CommandType
FROM
    ReportDefinition
WHERE
    CommandText LIKE '%Employee%'

Now we get only the reports that have the specified value in the CommandText node of the RDL/XML, with the report name and its directory location in the Report Manager UI. Just what was needed. I have wrapped this code up as a stored procedure for some of the MI department where I work so that they can locate the reports they need within a few moments, that they might have previously spent hours searching for.

* Note - If you are working on a SQL 2005 Report Server, you may need to alter the line
XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'
to
XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'