查询Oracle数据库的表和字段类型和长度等信息

// 通过user_tab_columns & user_col_comments 查询所有表和列等属性

SELECT * 
FROM user_tab_columns a, user_col_comments b
 WHERE a.TABLE_NAME = 'TEST02'
   AND b.table_name = 'TEST02';

所有属性如下：

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,HISTOGRAM,TABLE_NAME_1,COLUMN_NAME_1,COMMENTS

 

// 通过user_tab_columns & user_col_comments 查询字段的类型和长度信息

SELECT a.TABLE_NAME, b.COLUMN_NAME, a.DATA_TYPE, a.CHAR_USED, a.DATA_LENGTH, a.CHAR_LENGTH
FROM user_tab_columns a, user_col_comments b
 WHERE a.TABLE_NAME = 'TEST02' 
 AND b.table_name = 'TEST02';

 

属性说明：

完整的属性说明参考官方文档：https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2094.htm

TABLE_NAME	Name of the table, view, or cluster
COLUMN_NAME	Column name
DATA_TYPE	Datatype of the column
CHAR_USED	B | C. B indicates that the column uses BYTElength semantics. C indicates that the column uses CHAR length semantics. NULL indicates the datatype is not any of the following: CHAR VARCHAR2 NCHAR NVARCHAR2
DATA_LENGTH	ength of the column (in bytes)
CHAR_LENGTH	Displays the length of the column in characters. This value only applies to the following datatypes: CHAR VARCHAR2 NCHAR NVARCHAR