Report definitions(.rdl) are stored in the catalog table of ReportServer database.
The below query will extract all the information (including queries) from catalog table of report server and store them into a temp table
--clean up
IF OBJECT_ID('tempdb..#MySSRSQueriesTable') IS NOT NULL DROP Table #MySSRSQueriesTable
;WITH XMLNAMESPACES (
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2010/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
INTO #MySSRSQueriesTable
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)
Equally you can look into this article (
http://bretstateham.com/extracting-ssrs-report-rdl-xml-from-the-reportserver-database/ ) for Querying the RDL Contents
Hope that helps
--
Thanks & Regards,
RNA Team
Chamu, if this helps please login to Mark As Answer. | Alert Moderator