Searching SSRS Meta Data

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

Advertisements
Searching SSRS Meta Data

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s