Get Report Data Sources Details (TSQL)

DROP PROC usp_GetReportDataSourcesUsingAdHocSQL

GO

CREATE PROC usp_GetReportDataSourcesUsingAdHocSQL @ReportName NVARCHAR(850) = NULL

AS

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

Purpose: To retrieve the stored procedures used as data sources

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.

A loop (not very efficient) has been used here to cater for the fact

that reports may have been upgraded/transferred from a diffrent SSRS version

and may not have had their schema reference upgraded.

Author: Sankar Reddy/Jacob Sebastian

History: 14 May 2009 - Initial Issue

27 Jul 2012 - ChillyDBA - 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.

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

DECLARE

@Namespace NVARCHAR(500),

@SQL VARCHAR(MAX),

@ReportGUID VARCHAR(255)

CREATE TABLE #Results

(

ItemID INT IDENTITY(1,1),

ReportName VARCHAR(850),

CommandType VARCHAR(250),

CommandText VARCHAR(8000),

ReportDataSourceName 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

x.name AS ReportName,

x.value(''CommandType[1]'', ''VARCHAR(50)'') AS CommandType,

x.value(''CommandText[1]'',''VARCHAR(8000)'') AS CommandText,

x.value(''DataSourceName[1]'',''VARCHAR(50)'') AS DataSource

FROM

(

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

FROM ReportServer.dbo.Catalog C (NOLOCK)

WHERE C.Content is not null

AND C.Type != 3

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

) x

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

WHERE x.value(''CommandType[1]'', ''VARCHAR(50)'') IS NULL

ORDER BY x.name '

INSERT #Results

(

ReportName,

CommandType,

CommandText,

ReportDataSourceName

)

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,

CommandType,

CommandText,

ReportDataSourceName

FROM #Results

ORDER BY ItemID