Jacob's Blog

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Blogs

Upload Image

Close it

Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.


	Like

Invite Friends

Browse by Tags · View All

XML	112
TSQL	69
XQuery	69
XQuery Functions	67
XQuery Training	65
XQuery in TSQL	64
XQuery Tutorial	63
SQL Server XQuery	63
XQuery-Labs	57
BRH	38

Archive · View All

September 2008	32
August 2008	30
July 2008	21
August 2009	19
June 2009	19
May 2010	18
January 2009	15
January 2010	14
October 2008	14
June 2008	13

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (250) · Preferences

How to find all stored procedures used by Report Server?

Jan 16 2008 3:07PM by Jacob Sebastian

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.

Tags: XML, TSQL,

Previous: VARCHAR/NVARCHAR (N) vs (MAX)
Next: SQL Server KATMAI

Jacob Sebastian

1 · 100% · 32002

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

1 Comments

Thanks very much, Jacob. This is terrific - exactly what I was looking for. A small note - if you are developing for SQL Server 2008 R2, you will need to modify the XML namespaces at the top of the query. I have used: 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'

commented on Sep 30 2011 8:57AM

Simon Doubt

2701 · 0% · 3

Your Comment

Sign Up or Login to post a comment.

"How to find all stored procedures used by Report Server?" rated 5 out of 5 by 2 readers +Jacob Sebastian