How to find table name when knowing column name and column value in Oracle

Posted on September 19, 2014  •  1 minutes  • 193 words

A tester in my project asked me today for a little help: how to find a list of tables that has column name A with value B. So I wrote a short script and sent him. I thought I would share it here too, in case if might be useful to someone.

Usually cases like this, you will query from user_tab_cols or all_tab_cols for list of tables that has a column with that name and find out.

The query below will do exactly that but with the loop added to make searching easier. Make sure you have DBMS output enabled.

declare
  col_name varchar2(100) := 'COLUMN_NAME';
  search_value varchar2(100) := 'COLUMN_VALUE';
  cnt number := 0;
  cursor c is select distinct table_name from all_tab_cols where upper(column_name) = upper(col_name);
begin
  for e in c loop
    cnt := 0;
    execute immediate 
      'select count(' || col_name || ') from ' || e.table_name || ' where to_char(' || col_name || ') = to_char(''' || search_value || ''')'
    into cnt;
    
    dbms_output.put_line('Found ' || cnt || ' record(s) in ' || e.table_name);
  end loop;
end;

Sample output:

Found 0 record(s) in TABLE_NAME
Found 1 record(s) in TABLE_NAME1
Found 0 record(s) in TABLE_NAME2