Reports and SSRS - Get Report Data Sources

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:    Unknown
Date:        25 Jul 2012

Description

Retrieves a list of the data sources used by one or many reports.

I was a little thrown off balance with this one.  After the relative straightforward retrieval of other report metadata, I came to a bit of a brick wall when trying to find schema information on the DataSources table.  It appears that Microsoft deliberately haven't published this information to discourage the writing of queries against the ReportServer database.
Data Sources are initially listed as type #5 entries in the Catalog table, alongside Reports and other Report Server objects.  This listing contains only brief metadata (name, folder) but no connection-specific information.
The Data Sources table appears to be a way of modeling the 1..n relationship between Reports and Data Sources, and also stored connection information.  However, there are 2 columns that can join to the Catalog table (ItemID and LinkID), with the latter being optional.  I was unable to reliably unpick exactly what these relationships model, as there appeared to be attribute dependencies on some of them  ie if x = NULL then relationship is y, otherwise it is z
I'm sure these variations capture shared data sources and those with and without stored credentials, but nowhere could I find this out for certain.  The task was made more difficult as currently I have access to a ReportServer database but not the web site or report code to be able to check on assumptions.

I have therefore left the query at the very basic level and will revisit this code when I eventually discover the truth.

NOTE:  It is assumed that the Report Server Database has its default name of 'ReportServer' 

Code

Stored Procedure:


DROP PROC usp_GetReportDataSources
GO

CREATE PROC usp_GetReportDataSources @ReportName NVARCHAR(850) = NULL
AS

/****************************************************
Purpose:   To retreive the data sources for one or many reports.
           Uses the Catalog and DataSources tables in the report server
           database (assumed to be called by its default
           name of 'ReportServer')
          
           The DataSources table is not well documented (Microsoft do
           not publish the schema to discourage code like this),
           so this is a best-guess amalgamation from multiple code sources

Author:        Unknown
History:   Unknown
*****************************************************/
  
SELECT
    
C2.Name AS Data_Source_Name,
    
C1.Name AS Dependent_Item_Name,
    
C1.Path AS Dependent_Item_Path
FROM ReportServer.dbo.DataSource AS DS
INNER JOIN ReportServer.dbo.Catalog AS C1
  
ON DS.ItemID = C1.ItemID
    
AND DS.Link IN
              
(
                  
SELECT ItemID
                  
FROM ReportServer.dbo.Catalog
                    
WHERE TYPE = 5
              
) --Type 5 identifies data sources
FULL OUTER JOIN ReportServer.dbo.Catalog C2
  
ON DS.Link = C2.ItemID
WHERE C2.TYPE = 5
AND C1.Name LIKE '%' + ISNULL(@ReportName, '') + '%'
ORDER BY
    
C2.Name ASC,
    
C1.Name ASC;

ċ
usp_GetReportDataSources.sql
(1k)
Andy Hughes,
Jul 15, 2013, 1:30 PM
Comments