Finding a Word in any Table that Stores Code

This might come in handy if you have to find all the places in your code that a word is used. The following code generates a SQL query with multiple ‘Unions’ that can be executed to show all the tables and report IDs to be reviewed.

SQL-Generating Code:

/* change the word 'account' to the word you want to find in your code */
DEFINE word = account

SELECT 'SELECT '''                                                            -- literal 'select'
       || owner||'.'||table_name                                              -- owner.table name as literal in output
       || ''' AS tbl, report_id, report_title, report_desc, report_sql FROM ' -- literal column names and 'from'
       || owner||'.'||table_name                                              -- the table to pull the code from
       || q'[ WHERE REGEXP_LIKE(report_sql,'&word','i') UNION ALL]'           -- a literal piece of SQL code 
                                                                              -- You must use 'UNION ALL' with CLOBs
  FROM all_tab_columns                                -- look here to find
 WHERE table_name like 'PREP%'                        -- all tables that start with prep
   AND column_name = 'REPORT_SQL'                     -- that have this field which holds our code
   AND data_type   = 'CLOB'                           -- we cannot union together CLOBs and VARCHAR2s
   AND NOT REGEXP_LIKE (table_name,'[[:digit:]]') ;   -- toss those tables that have backup dates on them

SQL Code Generated:

This will generate the following, which can be copied and pasted into the editor area. You'll have to get rid of the last 'Union all' before executing.

SELECT 'SBX_STR_PRD.PREP_DR_ALTVIP' AS tbl, report_id, report_title, report_desc, report_sql FROM SBX_STR_PRD.PREP_DR_ALTVIP WHERE REGEXP_LIKE(report_sql,'account','i') UNION ALL
SELECT 'SBX_STR_PRD.PREP_DR_CENROLL' AS tbl, report_id, report_title, report_desc, report_sql FROM SBX_STR_PRD.PREP_DR_CENROLL WHERE REGEXP_LIKE(report_sql,'account','i') UNION ALL
SELECT 'SBX_STR_PRD.PREP_DR_DISTMNT' AS tbl, report_id, report_title, report_desc, report_sql FROM SBX_STR_PRD.PREP_DR_DISTMNT WHERE REGEXP_LIKE(report_sql,'account','i') UNION ALL

Sample Report of Code to be Reviewed:

It's easiest to paste this into Word to find and highlight code that might need to be changed.