Search SSRS Reports for Keywords
Microsoft’s SQL Server Reporting Services, colloquially known as SSRS, provides a great way to expose data from SQL Server in human-consumable form, typically via a web site. Generally, it’s a good idea to use a stored procedure as the source of data for SSRS Reports. In a large SSRS environment, it can be challenging to determine which reports use a specific stored procedure.
This code provides details about SSRS Reports where the definition of the report includes any specific keyword, such as the name of a stored procedure.
You’ll need access to the ReportServer database to run this code.
Searches ReportServer item definitions for the @search_term
Hannah Vernon 2022-05-11
DECLARE @search_term nvarchar(max) = N'%account_summary_by_age%';
[item_name] = c.[Name]
, [path] = c.[Path]
, [description] = c.[Description]
, [is_hidden] = c.[Hidden]
, [parent_item_id] = COALESCE(
SELECT TOP(1) pc.[Name]
FROM dbo.[Catalog] pc
WHERE pc.ItemID = c.ParentID
, [object_type] =
WHEN 1 THEN 'Folder'
WHEN 2 THEN 'Report'
WHEN 3 THEN 'File'
WHEN 4 THEN 'Linked Report'
WHEN 5 THEN 'Data Source'
WHEN 6 THEN 'Report Model'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Data Set'
WHEN 9 THEN 'Image'
ELSE CONVERT(varchar(20), c.[Type])
, [report_definition_xml] = CONVERT(xml, c.[Content])
, [link_source_id] = c.[LinkSourceID]
, [created_by] = uc.[UserName]
, [creation_date] = c.[CreationDate]
, [modified_by] = um.[UserName]
, [modified_date] = c.[ModifiedDate]
, [item_id] = c.[ItemID]
FROM dbo.[Catalog] c
INNER JOIN dbo.[Users] AS uc ON c.[CreatedByID] = uc.[UserID]
INNER JOIN dbo.[Users] AS um ON c.[ModifiedByID] = um.[UserID]
WHERE c.[Content] IS NOT NULL
AND CONVERT(nvarchar(max), CONVERT(xml, c.[Content], 0), 0) LIKE @search_term
ORDER BY c.[Name];
Thanks for visiting our site, and be sure to check out the other tools we’ve made available.
If you found this post useful, please
consider donating a small amount
to help keep the lights on and site running.