Greetings!

I recently needed to find all the reports that access a certain database.  Easy enough, just get all those with that data source, right? Well not exactly.  I have cross database calls happening on some of my reports, some meaning over 200 of them. I found this really cool way to do it and want to save it for the next time that I have to do it.  I hope you enjoy too!

--ItemBin gets the content as a varbinary(max)

WITH ItemBin AS
(
SELECT
ItemID,Name,[Type]
,CASE Type
WHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
ELSE 'Other'
END AS TypeDescription

,CONVERT(varbinary(max),Content) AS Content
FROM ReportServer.dbo.Catalog
WHERE Type IN (2,5,7,8)
),
--ItemNoBOM removes the BOM if it exists...this is some extra junk that you don't need right now.
ItemNoBOM AS
(
SELECT
ItemID,Name,[Type],TypeDescription
,CASE
WHEN LEFT(Content,3) = 0xEFBBBF
THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
ELSE
Content
END AS Content

FROM ItemBin )
--ItemXML gets the content in xml form
,ItemXML AS
(
SELECT
ItemID,Name,[Type],TypeDescription
,CONVERT(xml,Content) AS ContentXML
FROM ItemNoBOM
)
--We can then use the XML data type to extract the queries, along with their command and text and place them in a temp table
SELECT
ItemID,Name,[Type],TypeDescription,ContentXML

,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
INTO SSRSXMLTemp
FROM ItemXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemXML.ContentXML.nodes('//*:Query') Queries(Query)

-- Next we join them to the original Catalog table so I can get the Name and Path while also having the command and then filter out what I don't want
--From the command.

SELECT A.NAME, Path, CommandText
FROM SSRSXMLTemp A
INNER JOIN Catalog C ON A.ItemId =C.ItemId
WHERE CommandText LIKE '%Something I am looking for%'
ORDER BY Name