Credits:
Author: Sankar Reddy/Jacob Sebastian
Date: 14 May 2009
Description
Retrieves the stored procedures used as data sources for one or many reports.
The report xml is directly parsed for the information, so a namepsace is 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.
NOTE: It is assumed that the Report Server Database has its default name of 'ReportServer'
Code
Stored Procedure:
|
DROP PROC usp_GetReportDataSourcesUsingStoredProcedures
GO
CREATE PROC usp_GetReportDataSourcesUsingStoredProcedures @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 = 2
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)'') = ''StoredProcedure''
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
|
 Updating... usp_GetReportDataSourcesUsingStoredProcedures.sql (4k) Andy Hughes, Jul 28, 2012, 10:24 AM
|
