--表名 SELECT * FROM USER_TABLES WHERE LOWER(TABLE_NAME) LIKE '%kkkkk%'--记录的话,不好找了。
--查询当前用户 SELECT * FROM USER_TABLES WHERE LOWER(TABLE_NAME) LIKE '%kkkkk%' --查询所有用户 SELECT * FROM DBA_TABLES WHERE LOWER(TABLE_NAME) LIKE '%kkkkk%' 还有,你所说的记录是什么意思?
declare 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'; v_sql long; v_num number; v_table_name varchar2(30); v_column_name varchar2(30); begin open cv_tables; loop fetch cv_tables into v_table_name,v_column_name; exit when cv_tables%notfound; v_sql:='select count(*) from '||v_table_name||' where '||v_column_name||'='||'''kkkkk''' ; execute immediate v_sql into v_num; if v_num>=1 then dbms_output.put_line('table is '||v_table_name||' and column is '||v_column_name); end if; end loop; end; / 按照水哥的改的,他的思路很清晰
--表名
SELECT * FROM USER_TABLES
WHERE LOWER(TABLE_NAME) LIKE '%kkkkk%'--记录的话,不好找了。
SELECT * FROM USER_TABLES
WHERE LOWER(TABLE_NAME) LIKE '%kkkkk%'
--查询所有用户
SELECT * FROM DBA_TABLES
WHERE LOWER(TABLE_NAME) LIKE '%kkkkk%'
还有,你所说的记录是什么意思?
这个好像......
对,就是知道字段内容是kkkkk,但不知道字段名和表名,所以要全库查询。
http://topic.csdn.net/u/20101110/15/06f0d4d1-f537-4124-b2f5-aabd98b2d0df.html
user_tab_cols
注意里面的值大写
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';
v_sql long;
v_num number;
v_table_name varchar2(30);
v_column_name varchar2(30);
begin
open cv_tables;
loop
fetch cv_tables into v_table_name,v_column_name;
exit when cv_tables%notfound;
v_sql:='select count(*) from '||v_table_name||' where '||v_column_name||'='||'''kkkkk''' ;
execute immediate v_sql into v_num;
if v_num>=1 then
dbms_output.put_line('table is '||v_table_name||' and column is '||v_column_name);
end if;
end loop;
end;
/
按照水哥的改的,他的思路很清晰
WHERE LOWER(TABLE_NAME) LIKE '%kkkkk%'