PL/SQL find me right table and column based on a value :)

Motive

How many times did I write someone an email, and ask them where is a specific value stored in a database (table, column) and the answer came in best scenario tomorrow. So I came with the idea to write my PL/SQL block to find me where is that particular value stored.

Solution

I wrote simple PL/SQL anonymous block to find me the necessary table and column for my task.

SET SERVEROUTPUT ON;
DECLARE
  match_count INTEGER;

--the owner/schema of the tables you are looking at
  v_owner VARCHAR2( 255) :='CUSTOMER' ;

-- data type you look at (in CAPITAL)
-- VARCHAR2, NUMBER, etc.
  v_data_type VARCHAR2( 255) :='VARCHAR2' ;

--The value you are looking for with like "%" operator
  v_search_string VARCHAR2(4000) := '%TGP%' ;

BEGIN
  FOR t IN (SELECT atc.table_name
                  ,atc.column_name
                  ,atc.owner 
            FROM all_tab_cols atc
           WHERE atc.owner = v_owner
             AND data_type =  v_data_type
             -- esclude vir. columns
             AND atc.column_id is not null
             -- exclude views
             AND not exists (select 1 
                               from all_views 
                               where view_name = atc.table_name) ) LOOP

    EXECUTE IMMEDIATE
        'SELECT COUNT(*) FROM ' 
         || t.owner || '.'  ||t. table_name|| 
        ' WHERE UPPER("'||t.column_name ||'") LIKE  UPPER(:1)'
    INTO match_count
    USING v_search_string ;

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

  END LOOP;
END;
/

parameters

The code has 3 variables:

• v_owner - the schema where query will look for
• v_data_type - data type
• v_search_string - The value you are looking for with like "%" operator

query

Code executes FOR LOOP on a query to find table_name, column_name and table owner(schema).
The query uses upper parameters v_owner and v_data_type, also exclude virtual columns and views.

SELECT atc.table_name
                  ,atc.column_name
                  ,atc.owner 
            FROM all_tab_cols atc
           WHERE atc.owner = v_owner
             AND data_type =  v_data_type
             -- exclude vir. columns
             AND atc.column_id is not null
             -- exclude views
             AND not exists (select 1 
                               from all_views 
                               where view_name = atc.table_name)

EXECUTE IMMEDIATE

 EXECUTE IMMEDIATE
        'SELECT COUNT(*) FROM ' 
         || t.owner || '.'  ||t. table_name|| 
        ' WHERE UPPER("'||t.column_name ||'") LIKE  UPPER(:1)'
    INTO match_count
    USING v_search_string ;

Inside LOOP the code runs EXECUTE IMMEDIATE statement with concatenating table owner, table name, and column and parameter v_search_string. So for every column inside every table, it runs to check if there is close enough value to that I look for.

Output

If the is a match (> 1) output table_name, column_name and match count to dbms_output.

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

Also since dbms_output has to be turned on and has a limit, I set at the beginning of PL/SQL block

SET SERVEROUTPUT ON;

which means the ARGUMENT/VALUES that I will be passing inside dbms_output.put_line prints the argument on the main console (Script output).