oracle根据某个值搜索所有的表及列名

1、创建

create or replace procedure A_YANDANCHAN is  -- 名称A_YANDANCHAN可以改掉
  v_sql VARCHAR2(4000);
  v_tb_column VARCHAR2(4000);
  v_cnt NUMBER(18,0);
  v_key VARCHAR(300):= 'Material_factory_certificate';

  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 ''%'|| v_key ||'%'' ' 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' or t1.Data_Type='NCHAR' or t1.Data_type='Clob' or t1.DATA_TYPE='Blob')
   ORDER BY t1.Table_Name, t1.Column_ID;
BEGIN

   FOR i IN cur LOOP
    v_sql := i.str; 

    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)|| '条记录含有字串"' || v_key || '" ');
    END IF;
  END LOOP;

  EXCEPTION WHEN OTHERS THEN
  Begin

    dbms_output.put_line(v_sql);
    dbms_output.put_line(v_tb_column);
  END;

end A_YANDANCHAN; -- 注意名称要和上述的名称统一

2、在Procedures找到新建的A_YANDANCHAN，右击，选择Test

 

 

3、选择DBMS Output,执行