oracle查询表结构

SELECT t1.COMMENTS, t.colUMN_NAME, t.DATA_TYPE, t.DATA_LENGTH, t.NULLABLE, t.DATA_DEFAULT,t.COLUMN_ID

FROM User_Tab_Cols t, User_Col_Comments t1

WHERE t.table_name = t1.table_name

AND t.column_name = t1.column_name

and t.table_name='MASTOPATHYNEW'

order by t.column_id asc;

---

select

case

when user_tab_comments.comments is null then a.table_name

else a.table_name||'('||user_tab_comments.comments||')'

end as 表名,

f.comments as 列标注,

a.column_name as 列名,

a.data_type as 字段类型,

NVL(decode(a.data_type, 'NUMBER', a.data_precision, a.data_length),'') as 长度,

decode(e.key1, 'P', '√', '') as 主键,

a.nullable as 是否为空,

a.data_default as 默认值,

decode(e.key2, 'R', '√', '') as 外键,

NVL(a.data_scale,0) as 小数位数

from

user_tab_columns a,

user_col_comments f,

user_tab_comments,

( select

uc.table_name,

uc.constraint_name,

ucc.column_name,

uc.constraint_type key1,

uc.constraint_type key2

from

user_constraints uc,

user_cons_columns ucc

where

(uc.constraint_type='R'or uc.constraint_type='P')

and uc.constraint_name=ucc.constraint_name

and uc.owner=ucc.owner

and uc.table_name=ucc.table_name

and ucc.table_name not like 'BIN$%==$0'

) e

where

a.table_name = e.table_name(+)

and a.column_name = e.column_name(+)

and a.table_name = f.table_name

and a.column_name = f.column_name

and a.table_name = user_tab_comments.table_name

and a.table_name not like 'BIN$%==$0'

and a.table_name = 'CERVICAL_BASIC'

order by a.COLUMN_ID asc