Often times I am tasked with analyzing impacts when we have DML or Logic changes to tables in our EDW. With RedGate Search the Database side is covered and fairly straight forward but when it comes to investigating SSRS reports that may use a table or column the task becomes much more difficult.
I knew the query data used in the reports must be stored somewhere, the trick was finding out where and how to parse it. Luckily for me the another SQL user had found the solution so I don’t want to take credit. I just felt I needed to share because this made my analysis so much easier and pain free.
Derived from https://www.simple-talk.com/sql/reporting-services/administrating-sql-server-reporting-services—planning,-documenting-and-troubleshooting/
/**********************************************************************************
SSRS ReportServer Query to Return Reports which use Search Context in Query
**********************************************************************************/
;WITH XMLNAMESPACES
(DEFAULT ‘http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition’,
‘http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition’ AS REP
)
SELECT c.Path ,
c.Name ,
DataSetXML.value(‘@Name’, ‘varchar(MAX)’) DataSourceName ,
DataSetXML.value(‘REP:Query[1]/REP:CommandText[1]’, ‘varchar(MAX)’) CommandText
FROM ( SELECT ItemID ,
CAST(CAST(Content AS VARBINARY(MAX)) AS XML) ReportXML
FROM [ReportServer].[dbo].[Catalog]
WHERE TYPE = 2
) ReportXML
CROSS APPLY ReportXML.nodes(‘//REP:DataSet’) DataSetXML ( DataSetXML )
INNER JOIN [dbo].[Catalog] c ON ReportXML.ItemID = c.ItemID
— Search by part of the query text
WHERE ( DataSetXML.value(‘REP:Query[1]/REP:CommandText[1]’, ‘varchar(MAX)’) )
LIKE ‘%<insert column or table name here%’—- ENTER SEARCH TEXT HERE
sqlwizardmn 