1.获取表

Oracle:

1
2
3
4
5
6
7
select table_name from user_tables; //当前用户的表        table_name,tablespace_name,last_analyzed等 
select table_name from all_tables; //所有用户的表 ower,table_name,tablespace_name,last_analyzed等
select table_name from dba_tables; //包括系统表 ower,table_name,tablespace_name,last_analyzed等
--实例:
select table_name from dba_tables where owner='用户名'
列名:
ower,object_name,subobject_name,object_id,created,last_ddl_time,timestamp,status等

2.获取表字段

Oracle:

1
2
3
4
5
6
7
8
9
select * from user_tab_columns where Table_Name='用户表'; 
select * from all_tab_columns where Table_Name='用户表';
select * from dba_tab_columns where Table_Name='用户表';
user_tab_columns 列名:
table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等
all_tab_columns 列名:
ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等
dba_tab_columns 列名:
ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等

3.获取表注释

Oracle:

1
2
select * from user_tab_comments where Table_Name='用户表'; 
user_tab_comments 列名:table_name,table_type,comments

4.获取字段注释

Oracle:

1
2
select * from user_col_comments where Table_Name='用户表'; 
user_col_comments 列名:table_name,column_name,comments

MySQL:

1
select * from information_schema.COLUMNS where table_name = '表名' and table_schema = '数据库名'
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT
def EcoGeoDB azhdcd STTYBH 1 NO varchar 19 57 utf8 utf8_bin varchar(19) PRI select,insert,update,references 统一编号