-- 查看表中列含有指定字符。
SQL> select * from demo1;

NAME ID
------------------------------ ----------
???? 4
andy 1
andy1 2
andy2 3
liudehua 3
???? 5

6 rows selected.

SQL> select * from demo2;

NAME ID
------------------------------ ----------
andy 1
andy2 3
andy2 3
liudehua 3

SQL> select * from demo3;

NAME ID
------------------------------ ----------
andy 1
andy2 3
andy2 3
liudehua 3

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
BEGIN
dbms_output.put_line(v_sql);
dbms_output.put_line(v_tb_column);
END;
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

ITPub博客

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

最新文章