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 :-
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/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
andxmlcolumn.value('(Query/CommandType)[1]','VARCHAR(2500)') ='StoredProcedure'
