Get Report Data Sets

DROP PROC usp_GetReportDataSets

GO

CREATE PROC usp_GetReportDataSets @ReportName NVARCHAR(850) = NULL

AS

/****************************************************

Purpose: To retreive the data sets for one or many reports.

Uses the Catalog table in the report server

database (assumed to be called by its default

name of 'ReportServer')

The report xml is directly parsed for the information, so a namepsace

ise required. In order to make this code more protable, the namespace

is also parsed from the report code.

If many reports are in scope, then only the namespace from

the first report (alphabetically) is used

Author: Sorna Kumar Muthuraj

History: 9 Jul 2011 - Initial Issue

25 Jul 2012 - transferred to stored procedure and added code to dynamically

discover the namespace. Added a loop to ensure that servers with reports

having mixed version reports will still return data.

7 Oct 2014 - ChillyDBA - added report path to returned results.

*****************************************************/

DECLARE

@Namespace NVARCHAR(500),

@SQL VARCHAR(MAX),

@ReportGUID VARCHAR(255)

CREATE TABLE #Results

(

ItemID INT IDENTITY(1,1),

ReportName VARCHAR(850),

ReportPath VARCHAR(850),

DataSetName VARCHAR(250),

DataSourceName VARCHAR(250),

CommandText VARCHAR(250),

Fields VARCHAR(250),

DataField VARCHAR(250),

DataType VARCHAR(250)

)

SELECT @ReportGUID = MIN(CAST(ItemID AS VARCHAR(255)))

FROM Reportserver.dbo.Catalog (NOLOCK)

WHERE Name LIKE '%' + ISNULL(@ReportName, '') + '%'

AND Name IS NOT NULL

AND TYPE = 2

WHILE @ReportGUID IS NOT NULL

BEGIN

SELECT

@SQL = '',

@Namespace = ''

SELECT @Namespace= SUBSTRING(x.CatContent, x.CIndex, CHARINDEX('"',x.CatContent,x.CIndex+7) - x.CIndex)

FROM

(

SELECT TOP 1 CatContent = CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)))

,CIndex = CHARINDEX('xmlns="',CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content))))

FROM Reportserver.dbo.Catalog C (NOLOCK)

WHERE C.Content IS NOT NULL

AND C.TYPE = 2

AND C.ItemID = CAST(@ReportGUID AS UNIQUEIDENTIFIER)

ORDER BY C.name

) X

SELECT @Namespace = REPLACE(@Namespace,'xmlns="','') + ''

SELECT @SQL = 'WITH XMLNAMESPACES ( DEFAULT ''' + @Namespace + ''', ''http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'' AS rd )

SELECT ReportName = name

,ReportPath = path

,DataSetName = x.value(''(@Name)[1]'', ''VARCHAR(250)'')

,DataSourceName = x.value(''(Query/DataSourceName)[1]'',''VARCHAR(250)'')

,CommandText = x.value(''(Query/CommandText)[1]'',''VARCHAR(250)'')

,Fields = df.value(''(@Name)[1]'',''VARCHAR(250)'')

,DataField = df.value(''(DataField)[1]'',''VARCHAR(250)'')

,DataType = df.value(''(rd:TypeName)[1]'',''VARCHAR(250)'')

FROM ( SELECT C.Name,C.path, CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML

FROM ReportServer.dbo.Catalog C (NOLOCK)

WHERE C.Content is not null

AND C.Type = 2

AND C.ItemID = CAST(''' + @ReportGUID + ''' AS UNIQUEIDENTIFIER)

) a

CROSS APPLY reportXML.nodes(''/Report/DataSets/DataSet'') r ( x )

CROSS APPLY x.nodes(''Fields/Field'') f(df)

ORDER BY name '

INSERT #Results

(

ReportName,

ReportPath,

DataSetName,

DataSourceName,

CommandText,

Fields,

DataField,

DataType

)

EXEC(@SQL)

SELECT @ReportGUID = MIN(CAST(ItemID AS VARCHAR(255)))

FROM Reportserver.dbo.Catalog (NOLOCK)

WHERE CAST(ItemID AS VARCHAR(255)) > @ReportGUID

AND Name LIKE '%' + ISNULL(@ReportName, '') + '%'

AND Name IS NOT NULL

AND TYPE = 2

END

SELECT

ReportName,

ReportPath,

DataSetName,

DataSourceName,

CommandText,

Fields,

DataField,

DataType

FROM #Results

ORDER BY ItemID