Home › Discussions › Questions & Answers › Searching SQL in all Reports: Optimizing Impact Analysis with Blitz Report Queries

Searching SQL in all Reports: Optimizing Impact Analysis with Blitz Report Queries

Avatar photoCustomer Questions & Answers 7 replies

Our database team is planning a critical schema change involving a major custom table, ‘XX_CUSTOM_TAB’, and we urgently need to perform a comprehensive impact analysis. The complexity lies in identifying every single custom report, whether standard concurrent program based or defined in a custom reporting tool, that currently queries this table in its underlying SQL definition. Manually reviewing code packages and buried report files is not feasible within our timeframe. Is there an integrated solution allowing for rapid, centralized Searching SQL in all Reports to accurately map table dependencies and streamline our change control process?

7 replies

  • Support Enginatics

    This critical impact analysis scenario is greatly simplified when using a centralized reporting repository like Blitz Report. Because all report definitions, whether standard or highly customized, store their executable SQL code securely within dedicated Blitz Report tables, rapid searching across the entire report inventory is immediately possible.

  • So, instead of scanning file systems or application code, we can simply execute a query against the Blitz metadata tables? What specifically would that query look like to find references to ‘XX_CUSTOM_TAB’?

  • Support Enginatics

    Exactly. You can leverage the Blitz Report Query tool itself or use a simple SQL query against the underlying storage table (e.g., XXEN_REPORTS_V) filtering the column containing the report SQL definition, using `WHERE SQL_QUERY LIKE ‘%XX_CUSTOM_TAB%’`. This instantly returns a list of all dependent reports and their versions for review.

  • That is extremely powerful. Given that many of our reports were migrated from Discoverer or BI Publisher, does this search capability extend to those imported reports as well?

  • Support Enginatics

    Yes, once the SQL definition from any legacy report (Discoverer or BI Publisher) is imported into Blitz Report, its text is stored centrally and is fully indexed and searchable using this technique, providing a single source for managing all EBS report technical debt, effectively simplifying your impact analysis.

  • Can we also utilize this search capability to identify reports that are running inefficiently, for instance, by linking the SQL to DBA performance metrics?

  • Support Enginatics

    Absolutely. This central storage of the SQL query allows integration with diagnostic tools. For critical performance review, Blitz Report includes DBA AWR Blitz Reports that analyze high-impact SQL statements consuming significant database resources, linking those performance metrics directly back to the specific report definitions for targeted SQL tuning.

  • You must be logged in to reply to this post.