1.取表 SELECT "SYS"."TAB"."TNAME", "SYS"."TAB"."TABTYPE", "SYS"."TAB"."CLUSTERID" FROM "SYS"."TAB" ORDER BY "SYS"."TAB"."TABTYPE" ASC, "SYS"."TAB"."TNAME" ASC 2.取列 SELECT "SYS"."COL"."TNAME", "SYS"."COL"."COLNO", "SYS"."COL"."CNAME", "SYS"."COL"."COLTYPE", "SYS"."COL"."WIDTH", "SYS"."COL"."SCALE", "SYS"."COL"."PRECISION", "SYS"."COL"."NULLS", "SYS"."COL"."DEFAULTVAL", "SYS"."COL"."CHARACTER_SET_NAME" FROM "SYS"."COL" ORDER BY "SYS"."COL"."TNAME" ASC, "SYS"."COL"."COLNO" ASC
老大,是要找一个值? 做个过程,一个表一个字段地like,找到了就输出表名和字段名,呵呵。
先取到所有的表名 然后循环, 分别取到每个表的字段名 再循环, select column_name from table_name where column_name like '星期天'; 直到找到为止。。
select column_name from table_name where column_name like '星期天';如果 table_name是参数,而不是实际表名,存储过程编译不过去阿会有下面的错误啊,在怎么处理阿
Error: PL/SQL: ORA-00942: table or view does not exist
不明白你的全库是什么意思,下面的程序可以帮你搜索一个用户的所有表,会列出含有“星期天” 内容的所有表名和字段名。。 如果你的全库指的是一个数据库的所有用户,,那么你自己改一下程序即可。。 SQL> conn gene/gene 已连接。 SQL> select table_name from user_tables;TABLE_NAME ------------------------------ AA BBSQL> select * from aa; ID NAME ---------- -------- 1 ta 2 星期二SQL> select * from bb; ID MYDATE ---------- -------- 1 星期天SQL> create or replace procedure test as type ref_cursor is ref cursor; tablename_list ref_cursor; fieldname_list ref_cursor; foundtable ref_cursor; tablename user_tables.table_name%type; fieldname user_tab_columns.column_name%type; column_sql varchar2(500); begin open tablename_list for select table_name from user_tables; loop fetch tablename_list into tablename; exit when tablename_list%notfound; column_sql := 'select column_name from user_tab_columns where table_name='''||tablename||''''; open fieldname_list for column_sql; loop fetch fieldname_list into fieldname; exit when fieldname_list%notfound; column_sql := 'select '||fieldname||' from '||tablename||' where '||fieldname||' like ''%星期天%'' and rownum=1'; open foundtable for column_sql; loop fetch foundtable into fieldname; exit when foundtable%notfound; dbms_output.put_line(tablename||' '||fieldname); end loop; close foundtable; end loop; close fieldname_list; end loop; close tablename_list; end; /过程已创建SQL> set serveroutput on SQL> exec test BB 星期天PL/SQL 过程已成功完成。
不好意思,刚才的程序有点小错误,现改一改create or replace procedure test as type ref_cursor is ref cursor; tablename_list ref_cursor; fieldname_list ref_cursor; foundtable ref_cursor; tablename user_tables.table_name%type; fieldname user_tab_columns.column_name%type; column_sql varchar2(500); record_rownum number(1); begin open tablename_list for select table_name from user_tables; loop fetch tablename_list into tablename; exit when tablename_list%notfound; column_sql := 'select column_name from user_tab_columns where table_name='''||tablename||''''; open fieldname_list for column_sql; loop fetch fieldname_list into fieldname; exit when fieldname_list%notfound; column_sql := 'select rownum from '||tablename||' where '||fieldname||' like ''%星期天%'' and rownum=1'; open foundtable for column_sql; loop fetch foundtable into record_rownum; exit when foundtable%notfound; dbms_output.put_line(tablename||' '||fieldname); end loop; close foundtable; end loop; close fieldname_list; end loop; close tablename_list; end; /
DESC 表名
在all_tab_columns视图中!
SELECT "SYS"."TAB"."TNAME",
"SYS"."TAB"."TABTYPE",
"SYS"."TAB"."CLUSTERID"
FROM "SYS"."TAB"
ORDER BY "SYS"."TAB"."TABTYPE" ASC,
"SYS"."TAB"."TNAME" ASC
2.取列
SELECT "SYS"."COL"."TNAME",
"SYS"."COL"."COLNO",
"SYS"."COL"."CNAME",
"SYS"."COL"."COLTYPE",
"SYS"."COL"."WIDTH",
"SYS"."COL"."SCALE",
"SYS"."COL"."PRECISION",
"SYS"."COL"."NULLS",
"SYS"."COL"."DEFAULTVAL",
"SYS"."COL"."CHARACTER_SET_NAME"
FROM "SYS"."COL"
ORDER BY "SYS"."COL"."TNAME" ASC,
"SYS"."COL"."COLNO" ASC
做个过程,一个表一个字段地like,找到了就输出表名和字段名,呵呵。
然后循环,
分别取到每个表的字段名
再循环,
select column_name from table_name where column_name like '星期天';
直到找到为止。。
select column_name from table_name where column_name like '星期天';如果 table_name是参数,而不是实际表名,存储过程编译不过去阿会有下面的错误啊,在怎么处理阿
Error: PL/SQL: ORA-00942: table or view does not exist
内容的所有表名和字段名。。
如果你的全库指的是一个数据库的所有用户,,那么你自己改一下程序即可。。
SQL> conn gene/gene
已连接。
SQL> select table_name from user_tables;TABLE_NAME
------------------------------
AA
BBSQL> select * from aa; ID NAME
---------- --------
1 ta
2 星期二SQL> select * from bb; ID MYDATE
---------- --------
1 星期天SQL> create or replace procedure test
as
type ref_cursor is ref cursor;
tablename_list ref_cursor;
fieldname_list ref_cursor;
foundtable ref_cursor;
tablename user_tables.table_name%type;
fieldname user_tab_columns.column_name%type;
column_sql varchar2(500);
begin
open tablename_list for select table_name from user_tables;
loop
fetch tablename_list into tablename;
exit when tablename_list%notfound;
column_sql := 'select column_name from user_tab_columns where table_name='''||tablename||'''';
open fieldname_list for column_sql;
loop
fetch fieldname_list into fieldname;
exit when fieldname_list%notfound;
column_sql := 'select '||fieldname||' from '||tablename||' where '||fieldname||' like ''%星期天%'' and rownum=1';
open foundtable for column_sql;
loop
fetch foundtable into fieldname;
exit when foundtable%notfound;
dbms_output.put_line(tablename||' '||fieldname);
end loop;
close foundtable;
end loop;
close fieldname_list;
end loop;
close tablename_list;
end;
/过程已创建SQL> set serveroutput on
SQL> exec test
BB 星期天PL/SQL 过程已成功完成。
as
type ref_cursor is ref cursor;
tablename_list ref_cursor;
fieldname_list ref_cursor;
foundtable ref_cursor;
tablename user_tables.table_name%type;
fieldname user_tab_columns.column_name%type;
column_sql varchar2(500);
record_rownum number(1);
begin
open tablename_list for select table_name from user_tables;
loop
fetch tablename_list into tablename;
exit when tablename_list%notfound;
column_sql := 'select column_name from user_tab_columns where table_name='''||tablename||'''';
open fieldname_list for column_sql;
loop
fetch fieldname_list into fieldname;
exit when fieldname_list%notfound;
column_sql := 'select rownum from '||tablename||' where '||fieldname||' like ''%星期天%'' and rownum=1';
open foundtable for column_sql;
loop
fetch foundtable into record_rownum;
exit when foundtable%notfound;
dbms_output.put_line(tablename||' '||fieldname);
end loop;
close foundtable;
end loop;
close fieldname_list;
end loop;
close tablename_list;
end;
/