Using T-SQL find the Stored Procedure associated to an SSRS Report

Question

I had some help writing this query -- I'm at a bit of a loss because i'm trying to find the the query type or procedure used and i'm not sure what else to add to the query or how to change it.

SELECT      
    Ds.Name as Data_Source_Name,
    C2.Name AS Data_Source_Reference_Name,
    C.Name AS Dependent_Item_Name,
    C.Path AS Dependent_Item_Path,
    ds.*
FROM
    ReportServer.dbo.DataSource AS DS
INNER JOIN  
    ReportServer.dbo.Catalog AS C ON DS.ItemID = C.ItemID 
                                  AND DS.Link IN (SELECT ItemID 
                                                  FROM ReportServer.dbo.Catalog
                                                  WHERE Type = 5) --Type 5 identifies data sources
FULL OUTER JOIN 
    ReportServer.dbo.Catalog C2 ON DS.Link = C2.ItemID
WHERE
    C2.Type = 5
    AND c.name LIKE '%mkt%'
ORDER BY    
    C.Path, C2.Name ASC, C.Name ASC;

Please advise.

Answer 1

Based on my comment, give this a try, should get you moving in the right direction on how you can parse the xml and zero in on the specific command.

You might have to update the name spaces in the script below and also add your report name.

But try something like this:

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition') --You may have to change this based on you SSRS version
SELECT
    [Path],
    Name,
    report_xml.value( '(/Report/DataSources/DataSource/@Name)[1]', 'VARCHAR(50)' ) AS DataSource,
    report_xml.value( '(/Report/DataSets/DataSet/Query/CommandText/text())[1]', 'VARCHAR(MAX)' ) AS CommandText, 
    report_xml.value( '(/Report/DataSets/DataSet/Query/CommandType/text())[1]', 'VARCHAR(100)' ) AS CommandType, 
    report_xml
FROM
    (
    SELECT 
        [Path], 
        Name, 
        [Type],
        CAST( CAST( content AS VARBINARY(MAX) ) AS XML ) report_xml 
    FROM dbo.[Catalog]
    WHERE Content IS NOT NULL
    AND [Type] = 2
    ) x
WHERE 
--use below in where clause if searching for the CommandText.  Depending on how the report was developed I would just use the proc name and no brackets or schema.
--Example:  if you report was developed as having [dbo].[procName] just use LIKE '%procName%' below.  Because other reports could just have dbo.procName.
report_xml.value( '(/Report/DataSets/DataSet/Query/CommandText/text())[1]', 'VARCHAR(MAX)' ) LIKE '%Your Proc Name here%'
--comment out the above and uncomment below if know your report name and want to search for that specific report.
--[x].[Name] = 'The Name Of Your Report'