实现思路:
通过user_tab_columns 查出所有的表名,再遍历每个表的每个字段,看字段值是否等于要查的值。
declare
v_sql varchar2(1000);
data_count number;
begin
for cur_table in (select distinct table_name
from user_tab_columns a) loop
for cur_column in (select distinct column_name
from user_tab_columns a
where a.TABLE_NAME = cur_table.table_name) loop
v_sql := 'select count(*) from ' || cur_table.table_name ||
' where instr(' || cur_column.column_name ||
',''你要查的字段值'')>0';
execute immediate v_sql into data_count;
if (data_count > 0) then
dbms_output.put_line(cur_table.table_name);
dbms_output.put_line(cur_column.column_name);
dbms_output.put_line(data_count || ':' || v_sql);
end if;
end loop;
end loop;
end ;
执行结果:
[2019-07-15 19:35:42] tableName
[2019-07-15 19:35:42] columnName
[2019-07-15 19:35:42] 8:select count(*) from tableName where instr(columnName,'你要查的字段值')>0