November 17, 2017

SQL Server Reporting Services, List out all the data sources with Report Name and Path

In Enterprise application, we can have many reports hosted. Sometimes there are few customized reports too. Over period of time, there may be many Data Sources used by the different reports. To find out Report and its data source, we can use the following query: (Execute on ReportServer Database)
SELECT C2.NAME AS Data_Source_Name
 , C.NAME AS ReportName
 , C.Path AS ReportPath
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 JOIN ReportServer.dbo.CATALOG C2 ON DS.Link = C2.ItemID
WHERE C2.Type = 5
ORDER BY C2.NAME ASC
 , C.NAME ASC;
This will gives following result:

Reference : Tejas Shah http://www.SQLYoga.com

No comments:

Post a Comment