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
WHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
END AS TypeDescription
,CONVERT(varbinary(max),Content) AS Content
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.
WHEN LEFT(Content,3) = 0xEFBBBF
END AS Content
FROM ItemBin )
--ItemXML gets the content in xml form
,CONVERT(xml,Content) AS ContentXML
--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
,Query.value('(./*:CommandText/text())','nvarchar(max)') AS CommandText
--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