January 29, 2015 at 11:27 am
We are planning to get rid of a view in Management studio, But need to make sure none of the reports are using that view?
Since there are lots of reports on the server, Is there a way to find out which reports are using that particular view?
January 29, 2015 at 1:23 pm
I'm not sure of a standard method - searching the solution in BIDS doesn't identify matches within queries.
There are two methods:
1. Download an application that can search file contents, I suggest FileSeek (freemium, free version works fine), set "Path" as your reports solution folder, "Include files" as "*.rdl" and your view name as "Query", and click "Search".
2. You could do pretty much the same by running the following procedure on the ReportServer database (the database where SSRS stores its reports), where VIEWNAME is the name of your view - this would identify all reports with VIEWNAME in their query text, but would give the bonus of showing any reports that have been deployed on the server but are no longer in your solution (deleted):
-- Source: http://bretstateham.com/extracting-ssrs-report-rdl-xml-from-the-reportserver-database/
--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
SELECT
ItemID,Name,[Type]
,CASE Type
WHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
ELSE 'Other'
END AS TypeDescription
,CONVERT(varbinary(max),Content) AS Content
FROM ReportServer.dbo.Catalog
WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
SELECT
ItemID,Name,[Type],TypeDescription
,CASE
WHEN LEFT(Content,3) = 0xEFBBBF
THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
ELSE
Content
END AS Content
FROM ItemContentBinaries
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
SELECT
ItemID,Name,[Type],TypeDescription
,CONVERT(xml,Content) AS ContentXML
FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
SELECT
ItemID,Name,[Type],TypeDescription,ContentXML
,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM ItemContentXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)
where charindex('VIEWNAME', Query.value('(./*:CommandText/text())[1]','nvarchar(max)') ) > 0
January 29, 2015 at 2:52 pm
Works! But Can you explain what CTE 2 does ?
January 29, 2015 at 3:47 pm
Sure - it extracts the binary report data (the actual report) from the ReportServer database, converts it to XML and extracts the relevant fields for Query Text and Query Type and includes the full report data as XML. It then scans the query text for the relevant text (in this case your view name).
This is probably bad practice, and may not work if the report format changes in a future SSRS update, but it works for now.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply