How to find all stored procedures used by Report Server?
Posted on January 5th, 2008 | by Admin |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 (
‘http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition’,
‘http://schemas.microsoft.com/SQLServer/reporting/reportdesigner’ AS rd
)
SELECT
x.value(‘CommandType[1]‘, ‘VARCHAR(50)’) AS CommandType,
x.value(‘CommandText[1]‘,’VARCHAR(50)’) AS CommandText
FROM (
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.
Sorry, comments for this entry are closed at this time.