How to find all stored procedures used by Report Server?

Report definitions [RDL files] are stored in the "catalog" table of ReportServer database. This table has a field "content" which stores the report definition as an image/text value.

The following query will extract a list of all reports and the stored procedures used by them, by querying the catalog table of report server.

 

;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

FROM (

select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from ReportServer.dbo.Catalog

) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'

 

Note that I have applied a filter for 'StoredProcedure'. If you want to get all the information (including queries etc) you should remove this filter. you should also increase the size of the field to VARCHAR(MAX) to make sure that the text is not truncated.