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

Question

比如某个表的一个字段里有 wc111 这个值，
怎么搜索到这个表的这个字段

Answer 1

-- 首先：这个字段，不应该是数值类型和日期类型，也就是说：这个字段应该是字符串类型！(VARCHAR2、VARCHAR、CHAR）

Answer 2

-- 用下面的语句执行的结果，再次去执行！SELECT 'SELECT t."'||t1.Column_Name||'" as col_name, COUNT(t."'||t1.Column_Name||'") as cnt FROM "'||
        t1.table_name||'" t WHERE t."'||t1.column_name||'" like ''%wc111'''||' GROUP BY t."'||t1.Column_Name||'"'
        ||' union all ' AS SQL
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;-- 结果类似如下：(把最后的 union all 替换成分号（;)
 SELECT t."MOBILE" as col_name, COUNT(t."MOBILE") as cnt FROM "MUSIC_CLICK_STAT" t WHERE t."MOBILE" like '%wc111' GROUP BY t."MOBILE" union all
 SELECT t."IMSI" as col_name, COUNT(t."IMSI") as cnt FROM "MUSIC_CLICK_STAT" t WHERE t."IMSI" like '%wc111' GROUP BY t."IMSI" union all
 SELECT t."VERSION" as col_name, COUNT(t."VERSION") as cnt FROM "MUSIC_CLICK_STAT" t WHERE t."VERSION" like '%wc111' GROUP BY t."VERSION" union all
 SELECT t."MODEL" as col_name, COUNT(t."MODEL") as cnt FROM "MUSIC_CLICK_STAT" t WHERE t."MODEL" like '%wc111' GROUP BY t."MODEL" union all
 SELECT t."PMD_STR" as col_name, COUNT(t."PMD_STR") as cnt FROM "MUSIC_CLICK_STAT_MEM" t WHERE t."PMD_STR" like '%wc111' GROUP BY t."PMD_STR" union all
 SELECT t."PREFIX" as col_name, COUNT(t."PREFIX") as cnt FROM "PHONEAREAMAP" t WHERE t."PREFIX" like '%wc111' GROUP BY t."PREFIX" union all
 SELECT t."AREA_ID" as col_name, COUNT(t."AREA_ID") as cnt FROM "PHONEAREAMAP" t WHERE t."AREA_ID" like '%wc111' GROUP BY t."AREA_ID" union all
 SELECT t."AREA_NAME" as col_name, COUNT(t."AREA_NAME") as cnt FROM "PHONEAREAMAP" t WHERE t."AREA_NAME" like '%wc111' GROUP BY t."AREA_NAME" union all
 SELECT t."PROVINCE_ID" as col_name, COUNT(t."PROVINCE_ID") as cnt FROM "PHONEAREAMAP" t WHERE t."PROVINCE_ID" like '%wc111' GROUP BY t."PROVINCE_ID" union all
 SELECT t."PROVINCE_NAME" as col_name, COUNT(t."PROVINCE_NAME") as cnt FROM "PHONEAREAMAP" t WHERE t."PROVINCE_NAME" like '%wc111' GROUP BY t."PROVINCE_NAME" union all
 SELECT t."CARDTYPE" as col_name, COUNT(t."CARDTYPE") as cnt FROM "PHONEAREAMAP" t WHERE t."CARDTYPE" like '%wc111' GROUP BY t."CARDTYPE" union all
 SELECT t."KEYWORD" as col_name, COUNT(t."KEYWORD") as cnt FROM "TJ_KEY_SEARCH" t WHERE t."KEYWORD" like '%wc111' GROUP BY t."KEYWORD" union all
 SELECT t."UPPER_KEYWORD" as col_name, COUNT(t."UPPER_KEYWORD") as cnt FROM "TJ_KEY_SEARCH" t WHERE t."UPPER_KEYWORD" like '%wc111' GROUP BY t."UPPER_KEYWORD" union all
 SELECT t."CDATE" as col_name, COUNT(t."CDATE") as cnt FROM "TJ_MUSIC_REC_VW" t WHERE t."CDATE" like '%wc111' GROUP BY t."CDATE" union all
 SELECT t."PNAME" as col_name, COUNT(t."PNAME") as cnt FROM "TJ_MUSIC_REC_VW" t WHERE t."PNAME" like '%wc111' GROUP BY t."PNAME" union all
 SELECT t."VER" as col_name, COUNT(t."VER") as cnt FROM "TJ_MUSIC_USER" t WHERE t."VER" like '%wc111' GROUP BY t."VER";

Answer 3

--首先用下面的语句定义个游标，可以得到所有长度超过5（要搜索字符串长度）的字符类型列
select owner, table_name, column_name
  from all_tab_columns
 where data_type = 'VARCHAR'
    or data_type = 'VARCHAR2'
   and data_length > 5;--然后针对每个列利用动态SQL进行查找
select count(*) from sOwner.sTable from sColumn like '%wc111%'--思路就是这个样子滴，效率偶不负责，反正这个需求怎么也不可能有效率的解决办法了。

Answer 4

-- 我最后的语句是这样的：SELECT 'SELECT '''||'"'||t1.table_name||'"."'||t1.Column_Name||'"'||''''||' as col_name, COUNT(t."'||t1.Column_Name||'") as cnt FROM "'||
        t1.table_name||'" t WHERE t."'||t1.column_name||'" like ''%CLERK'''
        ||' union all ' AS SQL
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;-- 执行后，生成的SQL语句类似如下：
...
SELECT '"EMP"."ENAME"' as col_name, COUNT(t."ENAME") as cnt FROM "EMP" t WHERE t."ENAME" like '%CLERK' union all
SELECT '"EMP"."JOB"' as col_name, COUNT(t."JOB") as cnt FROM "EMP" t WHERE t."JOB" like '%CLERK' union all
SELECT '"EMP2"."ENAME"' as col_name, COUNT(t."ENAME") as cnt FROM "EMP2" t WHERE t."ENAME" like '%CLERK' union all
SELECT '"EMP2"."JOB"' as col_name, COUNT(t."JOB") as cnt FROM "EMP2" t WHERE t."JOB" like '%CLERK' union all
SELECT '"EMP3"."ENAME"' as col_name, COUNT(t."ENAME") as cnt FROM "EMP3" t WHERE t."ENAME" like '%CLERK' union all
SELECT '"EMP3"."JOB"' as col_name, COUNT(t."JOB") as cnt FROM "EMP3" t WHERE t."JOB" like '%CLERK' union all
SELECT '"EMP_DELTAS"."CHANGE_TYPE"' as col_name, COUNT(t."CHANGE_TYPE") as cnt FROM "EMP_DELTAS" t WHERE t."CHANGE_TYPE" like '%CLERK' union all
SELECT '"EMP_SAL_LOG"."ENAME"' as col_name, COUNT(t."ENAME") as cnt FROM "EMP_SAL_LOG" t WHERE t."ENAME" like '%CLERK' union all
SELECT '"EMP_SAL_LOG"."DNAME"' as col_name, COUNT(t."DNAME") as cnt FROM "EMP_SAL_LOG" t WHERE t."DNAME" like '%CLERK' union all
SELECT '"EMP_SAL_LOG"."MIN_SAL"' as col_name, COUNT(t."MIN_SAL") as cnt FROM "EMP_SAL_LOG" t WHERE t."MIN_SAL" like '%CLERK' union all
SELECT '"ERRS"."MODULE"' as col_name, COUNT(t."MODULE") as cnt FROM "ERRS" t WHERE t."MODULE" like '%CLERK' union all
SELECT '"ERRS"."ERRMSG"' as col_name, COUNT(t."ERRMSG") as cnt FROM "ERRS" t WHERE t."ERRMSG" like '%CLERK' union all
SELECT '"EXPLICIT_IS_BEST"."Y"' as col_name, COUNT(t."Y") as cnt FROM "EXPLICIT_IS_BEST" t WHERE t."Y" like '%CLERK' union all
SELECT '"FREEORDERUSER"."ORDERID"' as col_name, COUNT(t."ORDERID") as cnt FROM "FREEORDERUSER" t WHERE t."ORDERID" like '%CLERK' union all
...

Answer 5

select table_name, column_name 
from all_tab_columns 
where column_name like '%wc111%';这样不行吗？

Answer 6

你这条SQL是找到所有列名中包含wc111的列，而楼主你开始提的需求是要找到列的内容中包含wc111的列，所以你的这条SQL是不满足你的需求的。

Answer 7

DECLARE
  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 ''%CLERK%''' 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')
   -- AND t1.table_name='RUN_STATS'
   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)||' 含有字串"CLERK" ');
    END IF;
  END LOOP;
EXCEPTION WHEN OTHERS THEN
BEGIN
  dbms_output.put_line(v_sql);
  dbms_output.put_line(v_tb_column);
END;
END;
/

Answer 8

DECLARE
  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 ''%wc111%''' 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')
   -- AND t1.table_name='RUN_STATS'
   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)||' 含有字串"wc111" ');
    END IF;
  END LOOP;
EXCEPTION WHEN OTHERS THEN
BEGIN
  dbms_output.put_line(v_sql);
  dbms_output.put_line(v_tb_column);
END;
END;
/

Answer 9

DECLARE
  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 ''%CLERK%''' 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)|| '条记录含有字串"CLERK" ');
    END IF;
  END LOOP;
EXCEPTION WHEN OTHERS THEN
BEGIN
  dbms_output.put_line(v_sql);
  dbms_output.put_line(v_tb_column);
END;
END;
/

Answer 10

SQL> set serveroutput on
SQL> 
SQL> declare
  2  cursor cv_tables is select table_name,COLUMN_NAME from user_tab_columns t where Data_Type='CHAR' or Data_Type='VARCHAR2' or Data_Type='VARCHAR';
  3  v_sql long;
  4  v_num number;
  5  v_table_name varchar2(30);
  6  v_column_name varchar2(30);
  7  begin
  8   open cv_tables;
  9   loop
 10   fetch cv_tables into v_table_name,v_column_name;
 11   exit when cv_tables%notfound;
 12   v_sql:='select count(*) from '||v_table_name||' where '||v_column_name||'='||'''SMITH''' ;
 13   execute immediate v_sql into v_num;
 14   if v_num>=1 then
 15   dbms_output.put_line('table is '||v_table_name||' and column is '||v_column_name);
 16   end if;
 17   end loop;
 18  end;
 19  /
 
table is EMP and column is ENAME
 
PL/SQL procedure successfully completed
 
SQL> 

Answer 11

create or replace procedure god is
     cursor curs_tab is select * from user_all_tables ;   --找到系统中的所有表结构
     var_row curs_tab%rowtype;
     type  curs_col_ is ref cursor; --定义列的游标
     type curs_tab_temp is ref cursor;
     curs_col curs_col_;
     var_col_row user_tab_columns%rowtype;
     var_tab_temp curs_tab_temp;
     var_tab_temp_row number(10);
     len number(4):=0;
BEGIN
     if curs_tab%isopen then
          CLOSE curs_tab;
     end if;
     OPEN curs_tab;
     LOOP
       len:=len+1;
         FETCH curs_tab INTO var_row;
         EXIT WHEN curs_tab%NOTFOUND;         if curs_col %isopen then
            close curs_col;
         end if;         open curs_col for  'select *  from user_tab_columns where table_name='''||var_row.table_name||'''';
         loop
            fetch curs_col into var_col_row;
            exit when curs_col%notfound;
            --dbms_output.put_line( 'select count(*) from '||var_row.table_name||' where length('||var_col_row.COLUMN_NAME||')<1000  and '||var_col_row.COLUMN_NAME||'=''Errors in file''');
            open var_tab_temp for 'select count(*) from '||var_row.table_name||' where '||var_col_row.COLUMN_NAME||'||'' ''=''wc111''';
            fetch var_tab_temp into var_tab_temp_row;
            exit when var_tab_temp_row=0 ;
            dbms_output.put_line(var_row.table_name||'  '||var_col_row.COLUMN_NAME);
         end loop;
     END LOOP;
     Dbms_Output.put_line(' ====='||len);
     exception when others then  DBMS_OUTPUT.PUT_LINE('--------');
END;

Answer 12

SQL> declare
  2  str varchar2(1000);
  3  num number;
  4  begin
  5  for i in(select column_name,table_name from user_tab_cols where data_type in('CHAR','VARCHAR','VARCHAR2'))
  6  loop
  7  str:='select count(nvl('||i.column_name||',0)) from '||i.table_name||
  8  ' where instr('||i.column_name||','||'''wc111'',1)>0';
  9  execute immediate str into num;
 10  if num>0 then
 11  dbms_output.put_line('表名:'||i.table_name||' 列名'||i.column_name);
 12  end if;
 13  end loop;
 14  end;
 15  /
 
表名:TB 列名NAME
 
PL/SQL procedure successfully completed