查看哪些表的哪些列含有指定字符串（如‘andy’存在哪些表的哪些列中）

-- 查看表中列含有指定字符。

SQL> select * from demo1;

NAME                                   ID

------------------------------ ----------

????                                    4

andy                                    1

andy1                                   2

andy2                                   3

liudehua                                3

????                                    5

6 rows selected.

SQL> select * from demo2;

SQL> select * from demo3;

SQL> set serveroutput on

SQL>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 ''%andy%''' 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('table'||substr(v_tb_column,1,instr(v_tb_column,'.',1,1)-1)||' col'||substr(v_tb_column,instr(v_tb_column,'.',1,1)+1)||' has string"andy" ');

    END IF;

  END LOOP;

EXCEPTION WHEN OTHERS THEN

  dbms_output.put_line(v_sql);

  dbms_output.put_line(v_tb_column);

END;

/

结果输出：

table"DEMO1" col"NAME" has string"andy"

table"DEMO2" col"NAME" has string"andy"

table"DEMO3" col"NAME" has string"andy"

table"TEAM" col"MENTOR" has string"andy"

PL/SQL procedure successfully completed.

参考：http://bbs.csdn.net/topics/350154546

本文转自 张冲andy 博客园博客，原文链接：http://www.cnblogs.com/andy6/p/6230878.html   ，如需转载请自行联系原作者