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
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;
Found 0 record(s) in TABLE_NAME Found 1 record(s) in TABLE_NAME1 Found 0 record(s) in TABLE_NAME2