可以根据字段来查, SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE (COLUMN_NAME= '字段名' ) AND TABLE_NAME NOT IN ( SELECT VIEW_NAME FROM USER_VIEWS ) ORDER BY LENGTH( TABLE_NAME ) DESC;
DECLARE cs_tbl_col IS SELECT ALL_ALL_TABLES COLUMN_NAME FROM ALL_TAB_COLUMNS; r_tbl_col cs_tbl_col%rowtype; v_sql VARCHAR2(32767); v_cnt number; BEGIN OPEN cs_tbl_col;
loop fetch cs_tbl_col into r_tbl_col ; execute IMMEDIATE 'select count(1) into :1 from '|| r_tbl_col.table_name || ' where ' || r_tbl_col.COLUMN_NAME like ' || 'abcd' using v_cnt; if v_cnt > 0 then dbms_output.put_line('table=' || r_tbl_col.table_name || 'col = ' || r_tbl_col.COLUMN_NAME ); end if; end loop; END;
如何保证别的表里没有这个值--有这个值的表名都要。具体情况是这样子的,我现在刚学习一套系统,只知道值,字段名大概能猜得出,但不是很肯定,数据字典里还找不到。只能用这个办法了。可惜查询语句不会写。
cs_tbl_col IS SELECT ALL_ALL_TABLES COLUMN_NAME FROM ALL_TAB_COLUMNS;
r_tbl_col cs_tbl_col%rowtype;
v_sql VARCHAR2(32767);
v_cnt number;
BEGIN OPEN cs_tbl_col;
loop
fetch cs_tbl_col into r_tbl_col ;
execute IMMEDIATE 'select count(1) into :1 from '|| r_tbl_col.table_name || ' where ' || r_tbl_col.COLUMN_NAME like ' || 'abcd' using v_cnt;
if v_cnt > 0 then
dbms_output.put_line('table=' || r_tbl_col.table_name || 'col = ' || r_tbl_col.COLUMN_NAME );
end if;
end loop;
END;