17,377
社区成员
发帖
与我相关
我的任务
分享
--罗哥你考虑的很完善,顶你的,我只写了个简单的
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
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>
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;
/
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;
/
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;
/
-- 我最后的语句是这样的:
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
...
--首先用下面的语句定义个游标,可以得到所有长度超过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%'
--思路就是这个样子滴,效率偶不负责,反正这个需求怎么也不可能有效率的解决办法了。
-- 用下面的语句执行的结果,再次去执行!
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";