Listing Stored Procedures used by SSRS

The following SQL will allow you to list all stored procedures used by Reporting Services reports. Run this against the ReportServer database on the SQL Server your Reporting Services installation uses:

;with xmlnamespaces

(

default

'http://schemas.microsoft.com/sqlserver/reporting/2008/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

from (

       select

              name

              , cast(cast(content AS VARBINARY(MAX))as xml) as reportXML

       from

              ReportServer.dbo.Catalog

       where

              Name not like '%.gif'

              and Name not like '%.jpg'

              and Name not like '%.jpeg'

) a

cross apply reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)

where

       x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'

Note, the 4th line, it may be necessary to change "2008" to "2003", "2005", "2010" depending on the version(s) of Visual Studio / Report Builder / BIDS, SSDT you've used to create your reports.

Credit to Jacob Sebastian, I used his post as a basis here:
http://beyondrelational.com/modules/2/blogs/28/posts/10446/how-to-find-all-stored-procedures-used-by-report-server.aspx