oracle怎么查表构造,查询Oracle数据库 表构造

查询Oracle数据库 表结构

--简单查询

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;