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

Question

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

Answer 1

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;