Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Get a List of All Reports Using a Specific View Expand / Collapse
Author
Message
Posted Wednesday, April 3, 2013 8:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 3:09 PM
Points: 18, Visits: 60
Is there a way to get a list of all reports that are using a specific view?
Post #1438406
Posted Wednesday, April 3, 2013 10:05 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 1,486, Visits: 1,037
Yes, 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
SELECT *
FROM INFORMATION_SCHEMA.Routines
WHERE Routine_Definition LIKE '%MyViewName%'


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:
http://gallery.technet.microsoft.com/scriptcenter/42440a6b-c5b1-4acc-9632-d608d1c40a5c
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%'

Post #1438453
Posted Wednesday, April 3, 2013 10:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 3:09 PM
Points: 18, Visits: 60
I am using embedded datasets in my report and your code worked perfectly.

Thank you!
Post #1438464
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse