注:展示图DB工具为(DataGrip)
1.查询表字段&字段名称
--查询表字段&字段名称
select * from user_col_comments where TABLE_NAME='T_ORDER_VERIFICATION';
结果:
2.-所有表字段字段名称(包含视图)
--所有表字段字段名称(包含视图)
select TABLE_NAME,COMMENTS,COLUMN_NAME from user_col_comments;
结果:
3.表详细信息(字段,类型,长度等[!不含字段名称])
--表详细信息(字段,类型,长度等[!不含字段名称])
select * from ALL_TAB_COLUMNS where TABLE_NAME ='T_ORDER_VERIFICATION';
结果:
表整理
--表整体
select ALL_TAB_COLUMNS.COLUMN_ID AS 序号,
user_col_comments.COMMENTS AS 名称,
user_col_comments.COLUMN_NAME AS 字段,
ALL_TAB_COLUMNS.DATA_TYPE
|| '('
|| to_char(ALL_TAB_COLUMNS.DATA_LENGTH)
|| ')' AS 类型,
ALL_TAB_COLUMNS.NULLABLE AS 是否为空,
ALL_TAB_COLUMNS.DATA_DEFAULT AS 默认值
from user_col_comments
left join ALL_TAB_COLUMNS ON
ALL_TAB_COLUMNS.TABLE_NAME = user_col_comments.TABLE_NAME
and user_col_comments.COLUMN_NAME = ALL_TAB_COLUMNS.COLUMN_NAME
where user_col_comments.TABLE_NAME = 'PLMS_T_ORDER_VERIFICATION'
order by ALL_TAB_COLUMNS.COLUMN_ID;
图示:
4.user_tab_comments;表注释
-- user_tab_comments;表注释
select * from user_tab_comments
where TABLE_TYPE='VIEW';