Search Report definitions

  • 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.)?

  • 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/

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply