Searching all varchar columns for a string value

We had a requirement to search through all the varchar columns in our database for certain values as we wanted to rename some of the application data. We needed to understand if any instances of this data had been 'missed' during the analysis phase of the application. This script would be an additional check for this.

We knew we only had to check a subset of information so we narrowed down what needed to be searched so the script wouldn't take forever to run.

We knew that the table names all started with USER_ (nothing to do with oracle tables with similar names - thats just the application naming convention). The data we needed to check could all be derived from a 'lookup' table - though we had to only select rows that started with EET_ (this made things a little fiddly as underscore is of course a single character wildcard).

Anyway enough background here is the code (make sure serveroutput is on before you run this!)

DECLARE
  match_count INTEGER;

BEGIN
  FOR t IN (SELECT table_name, column_name FROM user_tab_cols where table_name like 'USER_%' and data_type = 'VARCHAR2') LOOP

    EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM '||t.table_name||' WHERE "'||t.column_name||'" in (select PARTY_AGREEMENT_NAME from party_agreement where PARTY_AGREEMENT_NAME like ''EET\_%'' escape ''\'')
'||'' INTO match_count;

    IF match_count > 0 THEN
      dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
    END IF;

  END LOOP;
END;
/

So looking through the code you can see that initially we are looking for and varchar2 columns in our schema. We then build up a select statement to check if the table.column from the cursor contains any of the values from our sub select.

This produces output similar to the following:

USER@DB>set serverout on
USER@DB>@datachecker
USER_EET_CONFIRM_MAPPING PARTY_AGREEEMENT 14

PL/SQL procedure successfully completed.

So we know that this column in this table has 14 rows which contain something from our sub select that we need to fix.

The script can easily be updated to look in other datatypes or take input from any other table containing values to check.

Hope this proves useful for someone else.