oracle根据字段值查找所有表,Oracle 如何搜索当前用户下所有表里含某个值的字段？...

Oracle 如何搜索当前用户下所有表里含某个值的字段？ create or replace procedure MY_Pro_SearchKeyWord is v_sql VARCHAR2(40

Oracle 如何搜索当前用户下所有表里含某个值的字段？

create or replace procedure MY_Pro_SearchKeyWord is

v_sql VARCHAR2(4000);

v_tb_column VARCHAR2(4000);

v_cnt NUMBER(18,0);

cursor cur is SELECT 'SELECT '''||'"'||t1.table_name||'"."'||t1.Column_Name||'"'||''''||' as col_name, NVL(COUNT(t."'||t1.Column_Name||'"),0) as cnt FROM "'||

t1.table_name||'" t WHERE t."'||t1.column_name||'" like ''%关键字%''' AS str

FROM cols t1 left join user_col_comments t2

on t1.Table_name=t2.Table_name and t1.Column_Name=t2.Column_Name

left join user_tab_comments t3

on t1.Table_name=t3.Table_name

WHERE NOT EXISTS ( SELECT t4.Object_Name FROM User_objects t4

WHERE t4.Object_Type='TABLE'

AND t4.Temporary='Y'

AND t4.Object_Name=t1.Table_Name )

AND (t1.Data_Type='CHAR' or t1.Data_Type='VARCHAR2' or t1.Data_Type='VARCHAR')

ORDER BY t1.Table_Name, t1.Column_ID;

BEGIN

FOR i IN cur LOOP

v_sql := i.str; -- 获取将要执行的SQL语句；

EXECUTE IMMEDIATE v_sql INTO v_tb_COLUMN, v_cnt;

IF v_cnt > 0 THEN

dbms_output.put_line('表：'||substr(v_tb_column,1,instr(v_tb_column,'.',1,1)-1)||' 列：'||substr(v_tb_column,instr(v_tb_column,'.',1,1)+1)||

'有 '||to_char(v_cnt)|| '条记录含有字串"关键字" ');

END IF;

END LOOP;

EXCEPTION WHEN OTHERS THEN

BEGIN

dbms_output.put_line(v_sql);

dbms_output.put_line(v_tb_column);

END;

end MY_Pro_SearchKeyWord;

，

本文原创发布php中文网，转载请注明出处，感谢您的尊重！