declare str varchar(1000); begin for i in (select table_name, column_name from dba_tab_columns where owner = 'SCOTT' and column_name = 'DEPTNO') loop insert into scott.temp_ta values (i.table_name, i.column_name); commit; end loop; end;
select table_name from dba_col_comments where column_name = '列名'
非常感谢,但是这个只是查处了有该列DEPTNO的所有表,但是我需要的是DEPTNO=‘20’重点在于“有该列并且该列的值等于指定值”。 declare str varchar(1000); v_i int; v_val int; begin v_val:=20; v_i:=0; for i in (select table_name, column_name from dba_tab_columns where owner = 'SCOTT' and column_name = 'DEPTNO') loop str:='select count(*) from '||i.table_name||' where '||i.column_name||' ='||v_val; execute immediate str into v_i; if v_i>0 then insert into scott.temp_ta values (i.table_name, i.column_name); end if; commit; end loop; end;
declare
str varchar(1000);
v_i int;
v_val int;
begin
v_val:=20;
v_i:=0;
for i in (select table_name, column_name
from dba_tab_columns
where owner = 'SCOTT'
and column_name = 'DEPTNO') loop
str:='select count(*) from '||i.table_name||' where '||i.column_name||' ='||v_val;
execute immediate str into v_i;
if v_i>0 then
insert into scott.temp_ta values (i.table_name, i.column_name);
end if;
commit;
end loop;
end;