January 21, 2021 at 7:58 pm
I know that there is a query you can run in SQL to search the SQL text procedures & objects for a specific string.
Is there a way to search the report definitions of all reports for a specific string (values, table references, etc.)?
January 22, 2021 at 3:36 pm
I don't have a server handy at the moment but what you are looking for is in the REPORTSERVER.DBO.CATALOG
This has the RDL XML (Report Definitions) which you can query. To get the raw XML your query will look like:
SELECT C.NAME
, CONVERT(NVARCHAR(MAX),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 LIKE ‘%REPORT_NAME%’
AND CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.CONTENT))) LIKE ‘%DESIRED_STRING%’
Note this site. The stored procs are under the "datasets" - the datasets hold either ad-hoc SQL or the procs.
https://www.biinsight.com/querying-ssrs-report-definition-using-t-sql/
-- Itzik Ben-Gan 2001
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply