写个过程吧,没办法直接查出来的 select table_name,column_name from user_tab_columns 用上面语句查询出当前用户下所有表的字段, 遍历各表的字段,找到符合条件的
你是不是想弄清楚别人做的项目 而又没有相关文档若是这样可以跟踪sql试试
DECLARE CURSOR cur_query IS select table_name,column_name from user_tab_columns ; strsql VARCHAR2(5000); v_number NUMBER; BEGIN FOR rec_query IN cur_query LOOP strsql := 'SELECT count(*) FROM '|| rec_query.table_name ||' WHERE '|| rec_query.column_name ||' LIKE ''%北京%''' ; execute immediate strsql INTO v_number; IF v_number > 0 THEN dbms_output.put_line(rec_query.table_name||'--'||rec_query.column_name); END IF;
END LOOP; END;
declare a number; sql_hard varchar2(2000); vl varchar2(20) := 's'; ---内容 vv number; begin for rec1 in (select table_name, column_name, data_type from user_tab_columns) loop if rec1.data_type = 'VARCHAR2' OR rec1.data_type = 'CHAR' THEN a := 0; sql_hard := 'select instr (''' || vl || ''',' || rec1.column_name ||') as vv from ' || rec1.table_name; IF A>0 THEN DBMS_OUTPUT.put_line('TABLE NAME----'||REC1.TABLE_NAME); DBMS_OUTPUT.put_line('COLUMN NAME----'||REC1.COLUMN_NAME); END IF ; EXECUTE IMMEDIATE sql_hard; sql_hard := ''; END IF; end loop; end;
select table_name,column_name from user_tab_columns
用上面语句查询出当前用户下所有表的字段,
遍历各表的字段,找到符合条件的
DECLARE
CURSOR cur_query IS
select table_name,column_name from user_tab_columns ;
strsql VARCHAR2(5000);
v_number NUMBER;
BEGIN
FOR rec_query IN cur_query LOOP
strsql := 'SELECT count(*) FROM '|| rec_query.table_name ||' WHERE '|| rec_query.column_name ||' LIKE ''%北京%''' ;
execute immediate strsql INTO v_number;
IF v_number > 0 THEN
dbms_output.put_line(rec_query.table_name||'--'||rec_query.column_name);
END IF;
END LOOP;
END;
a number;
sql_hard varchar2(2000);
vl varchar2(20) := 's'; ---内容
vv number;
begin
for rec1 in (select table_name, column_name, data_type
from user_tab_columns) loop
if rec1.data_type = 'VARCHAR2' OR rec1.data_type = 'CHAR' THEN
a := 0;
sql_hard := 'select instr (''' || vl || ''',' || rec1.column_name ||') as vv from ' || rec1.table_name;
IF A>0 THEN
DBMS_OUTPUT.put_line('TABLE NAME----'||REC1.TABLE_NAME);
DBMS_OUTPUT.put_line('COLUMN NAME----'||REC1.COLUMN_NAME);
END IF ;
EXECUTE IMMEDIATE sql_hard;
sql_hard := '';
END IF;
end loop;
end;