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

 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'