获取oracle数据库表、字段、约束的信息

广9567

–获取数据库中的表信息
select * from dba_tables;
select * from all_tables;
select * from user_tables;
–获取数据库中表的字段信息
select * from dba_tab_columns;
select * from all_tab_columns;
select * from user_tab_columns;
–获取约束的信息
select * from user_constraints;
select * from user_cons_columns;
–获取表注释信息
select * from dba_tab_comments;
select * from all_tab_comments;
select * from user_tab_comments;
–获取表字段注释信息
select * from dba_col_comments;
select * from all_col_comments;
select * from user_col_comments;

–示例：查询某个表中的主键字段名
select col.column_name
from user_constraints con,user_cons_columns col
where con.constraint_name = col.constraint_name
and con.constraint_type = ‘P’
and col.table_name = ‘TABLE_NAME’;

看oracle的脚本可以发现视图USER_TAB_COLUMNS就是根据视图USER_TAB_COLS创建的,
其它依次类推:
create or replace view sys.user_tab_columns as
select TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
     DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
     DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
     DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
     CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
     GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
     V80_FMT_IMAGE, DATA_UPGRADED
from USER_TAB_COLS
where HIDDEN_COLUMN = ‘NO’
因此使用时尽量使用USER_TAB_COLUMNS，以避免获取到oracle自行添加的隐藏字段。