Find tables & columns that will have the specific data in oracle

Question

I want to find the columns in all the tables that could have the specific data or value in Oracle.

Example:tom@gmail.com

How can I narrow down on the tables that might have the email value?

--TABLE OR VIEW DOES NOT EXIST

SET SERVEROUTPUT ON SIZE 100000

DECLARE
  match_count INTEGER;
-- Type the owner of the tables you are looking at
  v_owner VARCHAR2(255) :='GWEB';

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

-- Type the string you are looking at
  v_search_string VARCHAR2(4000) :='tom@gmail.com';

BEGIN
  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP

    EXECUTE IMMEDIATE 
    'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' = :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;

Are you asking how you narrow down the number of tables/columns to search (not sure you can beyond filtering on data type as you already are; could set minimum size I guess?) or about the ORA-00942 error? If you aren't running this as GWEB then you need to prefix the table name with the owner; and you might as well quote the identifiers just in case... — Alex Poole, Feb 10 '22 at 16:26
Does this answer your question? [Search All Fields In All Tables For A Specific Value (Oracle)](https://stackoverflow.com/questions/208493/search-all-fields-in-all-tables-for-a-specific-value-oracle) — astentx, Feb 10 '22 at 17:53

score 1 · Accepted Answer · answered Feb 10 '22 at 16:44

You:

Do not consider the owner of the table in the dynamic query.
May need to quote the identifiers.
Can also filter on the data_length.

Like this:

DECLARE
  match_count     INTEGER;
  v_owner         VARCHAR2(255)  := 'GWEB';
  v_data_type     VARCHAR2(255)  := 'VARCHAR2';
  v_search_string VARCHAR2(4000) :='tom@example.com';
BEGIN
  FOR t IN (
    SELECT table_name,
           column_name
    FROM   all_tab_cols
    WHERE  owner       = v_owner
    AND    data_type   = v_data_type
    AND    data_length >= LENGTH(v_search_string)
  )
  LOOP
    EXECUTE IMMEDIATE 
        'SELECT COUNT(*)'
      || ' FROM "'||v_owner||'"."'||t.table_name||'"'
      || ' WHERE "'||t.column_name||'" = :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;
/

db<>fiddle here

It worked like a charm and without any hassles,thanks a lot for fixing the issues and also showing a sample db to query from,Appreciate it — Alpha, Feb 10 '22 at 20:06

Find tables & columns that will have the specific data in oracle

1 Answers1