Find your ReportServer Stored Procedures

Here is a source I found that will show you all the stored procedures that are stored in your SQL Server Reporting Services.

 

This is a staple for me!  It can get pretty crazy, not knowing what stored procedure goes to what report!

 

 

;
with xmlnamespaces (
default 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' as rd
)
select  name,x.value('CommandType[1]','VARCHAR(50)') as CommandType,x.value('CommandText[1]','VARCHAR(50)') as CommandText,
        x.value('DataSourceName[1]','VARCHAR(50)') as DataSource
from    (select name,cast(cast(content as varbinary(max)) as xml) as reportXML
         from   NRDEV1.ReportServer.dbo.Catalog
         where  content is not null
                and TYPE = 2) a
cross apply reportXML.nodes('/Report/DataSets/DataSet/Query') r (x)
where   x.value('CommandType[1]','VARCHAR(50)') = 'StoredProcedure'
order by name