查询所有表的所有字段信息

select table_name from user_tables;

--查询所有表的所有字段信息

SELECT a.TABLE_NAME as 表名,

a.COLUMN_NAME as 列名 ,

a.DATA_TYPE as 数据类型,

a.DATA_LENGTH as 长度,

a.NULLABLE as 是否为空,

case when d.constraint_type = 'P' then 'Y'

else 'N'end 是否为主键,

a.nullable as 为空,

a.COLUMN_ID as 列序号,

b.comments as 备注

FROM USER_TAB_COLS a inner join user_col_comments b on b.TABLE_NAME=a.TABLE_NAME

and b.COLUMN_NAME=a.COLUMN_NAME left outer join user_cons_columns c on a.table_name = c.table_name

and a.column_name = c.column_name left outer join user_constraints d on c.constraint_name = d.constraint_name

where a.Table_Name in (

select table_name from user_tables

)

group by a.table_name

--查询单个表的所有字段信息

SELECT a.TABLE_NAME as 表名,

a.COLUMN_NAME as 列名 ,

a.DATA_TYPE as 数据类型,

a.DATA_LENGTH as 长度,

a.NULLABLE as 是否为空,

case when d.constraint_type = 'P' then 'Y'

else 'N'end 是否为主键,

a.COLUMN_ID as 列序号,

b.comments as 备注

FROM USER_TAB_COLS a inner join user_col_comments b on b.TABLE_NAME=a.TABLE_NAME

and b.COLUMN_NAME=a.COLUMN_NAME left outer join user_cons_columns c on a.table_name = c.table_name

and a.column_name = c.column_name left outer join user_constraints d on c.constraint_name = d.constraint_name

where a.Table_name = 'CODE_DICT'

--简单查询column信息

select a.column_id,

a.column_name,

a.data_type,

a.data_length,

a.data_precision,

a.data_scale,

a.nullable,

a.data_default

from user_tab_columns a where table_name = 'CODE_DICT'

order by column_id

--

select

a.column_name,a.data_type,a.data_length,a.data_precision,

a.data_Scale,a.nullable,a.data_default,b.comments,d.constraint_type

from

user_tab_columns a,user_col_comments b ,user_cons_columns c ,user_constraints d

where

a.table_name = b.table_name

and a.column_name = b.column_name

and a.table_name = c.table_name and a.column_name = c.column_name

and c.table_name = d.table_name and c.constraint_name = d.constraint_name

and a.table_name = 'CODE_DICT'

order by

column_id

select * from user_constraints d where d.table_name ='CODE_DICT'