oracle 表结构语句,oracle查询表结构语句

select o.table_name, tmp.comments, o.COLUMN_NAME, t.comments, o.DATA_TYPE

|| CASE TRIM(o.DATA_TYPE)

WHEN ‘DATE‘ THEN

‘‘

WHEN ‘NUMBER‘ THEN

case when o.DATA_PRECISION is not null then

‘(‘ || o.DATA_PRECISION || ‘,‘ || o.DATA_SCALE || ‘)‘

else ‘‘ end

WHEN ‘CHAR‘ THEN

‘(‘ || o.DATA_LENGTH || ‘)‘

WHEN ‘VARCHAR2‘ THEN

‘(‘ || o.DATA_LENGTH || ‘)‘

END AS DATA_TYPE,

o.NULLABLE,

o.SEGMENT_COLUMN_ID,

const.COLUMN_NAME,

case when const.COLUMN_NAME is null then ‘N‘ else ‘Y‘ end as PI

from user_tab_cols o

inner join user_col_comments t on (t.table_name=o.TABLE_NAME and t.column_name=o.COLUMN_NAME)

left join (select t.table_name, c.comments

from user_tables t

left join user_tab_comments c

on t.TABLE_NAME = c.table_name) tmp

on o.TABLE_NAME = tmp.table_name

left join (select cs.table_name,cs.column_name from user_cons_columns cs,

user_constraints uc

where cs.constraint_name=uc.constraint_name

and uc.constraint_type=‘P‘

) const

on (o.COLUMN_NAME=const.column_name and o.table_name=const.table_name)

where o.table_name in (‘TEST1‘)

order by o.table_name , o.SEGMENT_COLUMN_ID

原文：https://www.cnblogs.com/qinjf/p/8343683.html