SSRS Extract Datasets

ssrsExtractDatasets.sql

USE ReportServer
go
drop table if exists #reports;

/* 2016 Reports */
WITH XMLNAMESPACES (
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
,'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily' as df
,DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition'
),reports
AS
(
SELECT	c.path, c.Name, CAST(CAST(c.Content AS VARBINARY(max)) AS XML) AS reportXml
FROM	dbo.Catalog AS c
where		Type = 2
)
SELECT r.path AS ReportPath, r.name AS ReportName, dataset.value('@Name','nvarchar(1024)') AS DataSetName, commandText.value('.','nvarchar(1204)') AS SQLCommandName
INTO #reports
FROM reports AS r
CROSS APPLY r.reportXml.nodes('//Report//DataSets//DataSet') AS n1(dataset)
CROSS APPLY dataset.nodes('Query/CommandText') AS n2(commandText);

/* 2010 Reports */
WITH XMLNAMESPACES (
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
,'http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition' AS cl
,DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'
),reports
AS
(
SELECT	c.path, c.Name, CAST(CAST(c.Content AS VARBINARY(max)) AS XML) AS reportXml
FROM	dbo.Catalog AS c
where		Type = 2
)
INSERT INTO #reports
SELECT r.path AS ReportPath, r.name AS ReportName, dataset.value('@Name','nvarchar(1024)') AS DataSetName, commandText.value('.','nvarchar(1204)') AS SQLCommandName
FROM reports AS r
CROSS APPLY r.reportXml.nodes('//Report//DataSets//DataSet') AS n1(dataset)
CROSS APPLY dataset.nodes('Query/CommandText') AS n2(commandText);

/* 2008 Reports */
WITH XMLNAMESPACES (
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
,'http://schemas.microsoft.com/sqlserver/reporting/2008/01/componentdefinition' AS cl
,DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'
),reports
AS
(
SELECT	c.path, c.Name, CAST(CAST(c.Content AS VARBINARY(max)) AS XML) AS reportXml
FROM	dbo.Catalog AS c
where		Type = 2
)
INSERT INTO #reports
SELECT r.path AS ReportPath, r.name AS ReportName, dataset.value('@Name','nvarchar(1024)') AS DataSetName, commandText.value('.','nvarchar(1204)') AS SQLCommandName
FROM reports AS r
CROSS APPLY r.reportXml.nodes('//Report//DataSets//DataSet') AS n1(dataset)
CROSS APPLY dataset.nodes('Query/CommandText') AS n2(commandText);

select * 
from #reports as r;