Blog TIL Now Tags Projects About

Finding where embedded datasets (sprocs) are used across reports:

WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
C.Name AS ReportName,
DS.value('@Name', 'VARCHAR(255)') AS DataSetName,
Q.value('CommandText[1]', 'VARCHAR(MAX)') AS CommandText
FROM
dbo.Catalog AS C
CROSS APPLY
(SELECT CAST(CAST(C.Content AS VARBINARY(MAX)) AS XML)) AS ReportXML(X)
CROSS APPLY
ReportXML.X.nodes('/Report/DataSets/DataSet') AS DataSets(DS)
CROSS APPLY
DataSets.DS.nodes('Query') AS Queries(Q)
WHERE
C.Type = 2 -- Type 2 indicates a Report
AND Q.value('CommandText[1]', 'VARCHAR(MAX)') LIKE '%' + 'spRptYourProcName' + '%';