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...
Andy Hughes, Jul 28, 2012, 10:24 AM
|