SQLServerCentral » Reporting Services » Reporting Services » How to Get a List of All Reports Using a Specific ViewInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralTue, 26 Sep 2017 16:01:58 GMT20How to Get a List of All Reports Using a Specific Viewhttps://www.sqlservercentral.com/Forums/FindPost1438406.aspxIs there a way to get a list of all reports that are using a specific view?Wed, 03 Apr 2013 10:22:14 GMTdedeodRE: How to Get a List of All Reports Using a Specific Viewhttps://www.sqlservercentral.com/Forums/FindPost1438464.aspxI am using embedded datasets in my report and your code worked perfectly.
Thank you!Wed, 03 Apr 2013 10:22:14 GMTdedeodRE: How to Get a List of All Reports Using a Specific Viewhttps://www.sqlservercentral.com/Forums/FindPost1438453.aspxYes, but how you get that info depends.
When creating your data sources in these reports did you use Embedded SQL? or Stored Procedures.
If you used only stored procedures simply query Information Schema DMV
[code="sql"]SELECT *
FROM INFORMATION_SCHEMA.Routines
WHERE Routine_Definition LIKE '%MyViewName%'[/code]
If you used embedded sql you can query the Report Server catalog table to find the view in the dataset information
CAUTION: This query is kind of intense on the sql server, use caution if running on production server.
Check out this link:
[url=http://gallery.technet.microsoft.com/scriptcenter/42440a6b-c5b1-4acc-9632-d608d1c40a5c]http://gallery.technet.microsoft.com/scriptcenter/42440a6b-c5b1-4acc-9632-d608d1c40a5c[/url]
[code="sql"]WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
SELECT
ReportName
,DataSetName
,DataSourceName
,CommandText
FROM (
SELECT ReportName = name
,DataSetName = x.value('(@Name)[1]', 'VARCHAR(250)')
,DataSourceName = x.value('(Query/DataSourceName)[1]','VARCHAR(250)')
,CommandText = x.value('(Query/CommandText)[1]','VARCHAR(250)')
FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
FROM ReportServer.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
--AND C.Name = '' Or Supply Report Name Here
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r ( x )
) T
WHERE CommandText LIKE '%MyViewName%'[/code]Wed, 03 Apr 2013 10:05:40 GMTRay M