Question

如何查询oracle一个数据库中包含有某个特定值的所有表及字段名

Answer 1

declare

v_Sql varchar2(2000);

v_count number;

begin

for xx in (select t.OWNER, t.TABLE_NAME, t.COLUMN_NAME

from dba_tab_columns t

where t.OWNER = 'SCOTT') loop

begin

v_Sql := 'select count(1) from ' || xx.owner || '.' || xx.table_name ||

' where ' || xx.column_name || ' like ''%TEST%'' ';

execute immediate v_Sql

into v_count;

if (v_count >= 1) then

dbms_output.put_line(xx.table_name || ':' || xx.column_name);

end if;

exception

when others then

null;

end;

end loop;

end;