DECLARE @RetVal TABLE
(
REPORT_PATH VARCHAR(500),
DATASETNAME VARCHAR(200)
);
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition')
--Extract all the Datasets of a report
INSERT INTO @RetVal
SELECT C.path AS REPORT_PATH,
T2.DN.value('.', 'varchar(200)') AS DataSetName
FROM dbo.Catalog c
CROSS APPLY (SELECT CONVERT(XML, CONVERT(VARBINARY(max), c.content)) xmlCriteria) a
CROSS APPLY A.xmlCriteria.nodes('//DataSets/DataSet/@Name') AS T2(DN)
WHERE C.Type=2 --type 2 RDL
DECLARE @REPORT_PATH VARCHAR(200)
DECLARE @DataSetName VARCHAR(200)
DECLARE DataSets CURSOR FOR
SELECT * FROM @RetVal
OPEN DataSets
FETCH NEXT FROM DataSets INTO @REPORT_PATH, @DataSetName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @DataSetName;
DECLARE @A VARCHAR(10)='';
--Extract all the parameters of the report Datasets
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition')
SELECT @REPORT_PATH AS Report_Path,
@DataSetName AS Dataset,
T3.QP.value('.', 'varchar(200)') AS Dataset_Parameter,
T4.CT.value('.', 'varchar(200)') AS Command_Text
FROM dbo.Catalog c
CROSS APPLY (SELECT CONVERT(XML, CONVERT(VARBINARY(max), c.content)) xmlCriteria) a
CROSS APPLY A.xmlCriteria.nodes('//DataSets/DataSet[@Name=sql:variable("@DataSetName")]/Query/QueryParameters/QueryParameter/@Name')AS T3(QP)
CROSS APPLY A.xmlCriteria.nodes('//DataSets/DataSet[@Name=sql:variable("@DataSetName")]/Query/CommandText')AS T4(CT)
WHERE C.path = @REPORT_PATH
FETCH NEXT FROM DataSets INTO @REPORT_PATH, @DataSetName
END
CLOSE DataSets
DEALLOCATE DataSets
SSRS Reporting Service 报表参数和数据集信息抽取
最新推荐文章于 2020-07-25 22:23:45 发布