Listing all Data Sources and their Dependencies (reports, items, etc) in SQL Server 2008 R2

I am new to SQL Server, and I am sorry if there is an obvious solution to my question but I can't seem to find it. I am looking to generate a report (or list) of all the data sources and their individual dependencies on an SQL Server 2008 R2 (reporting server). I know that I can access each individual data source to get a list of all the items that are dependent on it. I have done this in the past but it is time consuming. Is there a way to get a report that would display all the data sources and their dependent items? Thanks in advance, Marwan

Marwan مروان asked Mar 9, 2012 at 17:29 Marwan مروان Marwan مروان 2,211 8 8 gold badges 32 32 silver badges 40 40 bronze badges

What would you categorize as a dependent item? Only models, datasets, and reports, or are there others?

Commented Mar 9, 2012 at 19:14

@beargle - I would define a dependent item as any report that uses the data source. Currently, each data source has a "View Dependent Items" in its menu. So anything that would be listed in that would qualify as an dependent item. I hope that helps clarify my question/request. Thanks.

Commented Mar 9, 2012 at 19:25

3 Answers 3

The following (which was modified from what beargle posted earlier) does what I was looking for. This will list all the data sources by their actual name, and all their dependent items:

SELECT C2.Name AS Data_Source_Name, C.Name AS Dependent_Item_Name, C.Path AS Dependent_Item_Path FROM ReportServer.dbo.DataSource AS DS INNER JOIN ReportServer.dbo.Catalog AS C ON DS.ItemID = C.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 ORDER BY C2.Name ASC, C.Name ASC;
answered Mar 19, 2012 at 18:42 Marwan مروان Marwan مروان 2,211 8 8 gold badges 32 32 silver badges 40 40 bronze badges @beargle - Thanks for helping me figure this out (^_^) Commented Mar 19, 2012 at 18:51 Worked in first try. Excellent answer. All I had to do is to change the database name. Commented Jul 10, 2017 at 17:35

This query should be run against the ReportServer database

SELECT DS.Name AS DatasourceName, C.Name AS DependentItemName, C.Path AS DependentItemPath FROM ReportServer.dbo.Catalog AS C INNER JOIN ReportServer.dbo.Users AS CU ON C.CreatedByID = CU.UserID INNER JOIN ReportServer.dbo.Users AS MU ON C.ModifiedByID = MU.UserID LEFT OUTER JOIN ReportServer.dbo.SecData AS SD ON C.PolicyID = SD.PolicyID AND SD.AuthType = 1 INNER JOIN ReportServer.dbo.DataSource AS DS ON C.ItemID = DS.ItemID WHERE DS.Name IS NOT NULL ORDER BY DS.Name;

The dependent items page in Report Manager executes the dbo.FindItemsByDataSource stored procedure, supplying these parameters: ItemID = and AuthType = 1 . The above query is a hacked version of the query used by this stored procedure to remove the data source specific ID. This allows dependent items to be returned for all data sources. I removed the data sources themselves from the results with DS.Name IS NOT NULL