Reports and SSRS - Get Report Data Sources Details (TSQL)

Applicability:

                 SQL Server 2000:        Not Tested        
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Sankar Reddy/Jacob Sebastian
Date:        14 May 2009

Description

Retrieves the ad-hoc TSQL 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_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

ċ
usp_GetReportDataSourcesUsingAdHocSQL.sql
(4k)
Andy Hughes,
Jul 28, 2012, 10:29 AM
Comments