--简单查询

select *
? from ALL_TAB_COLS A
?where lower(A.Table_Name) = '要查询的表名';

?

--综合查询

select C.OWNER as 用户名,
?????? A.TABLE_NAME as 表名,
?????? E.COMMENTS as 表中文名,
?????? A.COLUMN_NAME as 列名,
?????? decode(A.DATA_TYPE,
????????????? 'CHAR',
????????????? A.DATA_TYPE || '(' || A.DATA_LENGTH || ')',
????????????? 'DATE',
????????????? A.DATA_TYPE,
????????????? 'NUMBER',
????????????? A.DATA_TYPE || '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE || ')',
????????????? 'VARCHAR2',
????????????? A.DATA_TYPE || '(' || A.DATA_LENGTH || ')') as 数据类型,
?????? decode(C.COLUMN_NAME, null, '', '是') as 主键,
?????? decode(D.INDEX_NAME, null, '', '是') as 索引,
?????? decode(A.NULLABLE, 'N', '', 'Y', '可空') as 可为空,
?????? B.comments as 备注
? from sys.user_tab_cols A,
?????? sys.user_col_comments B,
?????? (select col.column_name, c.table_name, col.owner
????????? from user_constraints c, user_cons_columns col
???????? where c.constraint_name = col.constraint_name
?????????? and c.constraint_type = 'P') C,
?????? (select ind.index_name, c.table_name
????????? from user_constraints c, user_indexes ind
???????? where c.table_name = ind.table_name
?????????? and c.constraint_type = 'P') D,
?????? sys.user_tab_comments E
?where lower(A.TABLE_NAME) = '要查询的表名'
?? and A.TABLE_NAME = B.table_name
?? and A.COLUMN_NAME = B.column_name
?? and A.Table_Name = E.TABLE_NAME
?? and A.Table_Name = C.TABLE_NAME(+)
?? and A.COLUMN_NAME = C.COLUMN_NAME(+)
?? and A.Table_Name = D.TABLE_NAME(+)
?? and A.COLUMN_NAME = D.INDEX_NAME(+)
?order by A.TABLE_NAME, A.Column_Id;