Is there any method to find all the table in database that has column with specific values eg. EMP_NAME = ABC?

Question

select table_name
from user_tab_columns
where column_name IN ('EMP_NAME');

This query gives around 150 table names. I want to find all the tables that has column "EMP_NAME" = ABC and delete it. How do I fetch all the tables that has value on the column "EMP_NAME" = ABC ?

Answer 1

For example: I'll create emp_temp table and modify one of its rows so that its ENAME column contains LITTLEFOOT - that's what I want to remove.

SQL> create table emp_temp as select * from emp;

Table created.

SQL> update emp_temp set ename = 'LITTLEFOOT' where ename = 'KING';

1 row updated.

Let's do it:

SQL> set serveroutput on
SQL> declare
  2    l_str varchar2(500);
  3    l_cnt number := 0;
  4  begin
  5    for cur_r in (select u.table_name, u.column_name
  6                  from user_tab_columns u, user_tables t
  7                  where u.table_name = t.table_name
  8                    and u.column_name = 'ENAME'
  9                 )
 10    loop
 11      l_str := 'SELECT COUNT(*) FROM ' || cur_r.table_name ||
 12              ' WHERE ' || cur_r.column_name || ' =  ''LITTLEFOOT''';
 13
 14      execute immediate (l_str) into l_cnt;
 15
 16      if l_cnt > 0 then
 17        dbms_output.put_line(l_cnt ||' : ' || cur_r.table_name);
 18       execute immediate 'delete from ' || cur_r.table_name ||
 19         ' where ' ||cur_r.column_name || ' = ''LITTLEFOOT''';
 20      end if;
 21    end loop;
 22  end;
 23  /
1 : EMP_TEMP

PL/SQL procedure successfully completed.

SQL> select * From emp_temp where ename = 'LITTLEFOOT';

no rows selected

SQL>

Apparently, I've been deleted. Terminated. But I'll be back.