Oracle 数据库查询表下包含某个值

转载博客：https://blog.csdn.net/qiuchw/article/details/96009535

Oracle 数据库查询表下包含某个值

declare
  v_sql  varchar2(1000);
  data_count number;
begin
  for cur_table in (select distinct  table_name
                      from user_tab_columns a) loop
    for cur_column in (select distinct column_name
                         from user_tab_columns a
                        where a.TABLE_NAME = cur_table.table_name) loop
      v_sql := 'select count(*) from ' || cur_table.table_name ||
               '  where instr(' || cur_column.column_name ||
               ',''17780'')>0';
      execute immediate v_sql into data_count;
      if (data_count > 0) then
        dbms_output.put_line(cur_table.table_name);
        dbms_output.put_line(cur_column.column_name);
        dbms_output.put_line(data_count || ':' || v_sql);
      end if;
    end loop;
  end loop;
end ;

------------------------第二种查询方法----------------------

DECLARE  
CURSOR cur_query IS  
  SELECT table_name, column_name, data_type FROM user_tab_columns;  
  a NUMBER;  
  sql_hard VARCHAR2(2000);  
  vv NUMBER;  
BEGIN  
  DBMS_OUTPUT.ENABLE(buffer_size => null);
  FOR rec1 IN cur_query LOOP  
  a:=0;  
  IF rec1.data_type ='VARCHAR2' THEN  
  a := 1;  
  END IF;  
  IF a>0 THEN  
  sql_hard := '';  
  sql_hard := 'SELECT COUNT(*) FROM  '|| rec1.table_name ||' WHERE '  ||rec1.column_name  || '=''查询的值''';
  dbms_output.put_line(sql_hard);   
  EXECUTE IMMEDIATE sql_hard INTO vv;  
  IF vv > 0 THEN  
   dbms_output.put_line('[字段值所在的表.字段]:['||rec1.table_name||'].['||rec1.column_name||']');  
  END IF;   
  END IF;  
  END LOOP;  
END;