FIND OBJECTS BEING REFERRED BY SSRS REPORT

Hi Everyone

In the daily team meeting today, we had a requirement to find out the stored procedure used/referenced by SSRS reports. My Team Lead suggest why cant we write a sql to search the xml of the reports.

Upon googling I came across this beautiful article by :-

https://www.mssqltips.com/sqlservertip/3693/find-tables-columns-or-stored-procedures-used-in-a-sql-server-reporting-services-report/

Suggest everyone to go through this to understand the code.

Here is the snipped which would give list of sp's that are used inside a report :-

Declare @ReportName VARCHAR(100)

Set @ReportName = 'QuarterlySalesPerformance'

;WITH XMLNAMESPACES

( DEFAULT

  ' http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'

, 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS ReportDefinition )

SELECT 

distinct

xmlcolumn.value('(Query/CommandText)[1]','VARCHAR(2500)') AS CommandText

FROM ( 

      SELECT C.Name

      ,c.Path

      ,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML

      FROM  dbo.Catalog C

      WHERE  C.Content is not null

      AND  C.Type = 2

      ) CATDATA

CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') xmltable ( xmlcolumn )

WHERE

CATDATA.Name = @ReportName

and xmlcolumn.value('(Query/CommandType)[1]','VARCHAR(2500)') ='StoredProcedure'