Oracle 查询表名、表注释、字段名、字段类型、字段默认值、字段备注

查询表名和注释

1
2
3
4
5
6
select
ut.table_name as "表名",
utc.comments as "表注释"
from user_tables ut LEFT OUTER JOIN user_tab_comments utc ON ut.table_name = utc.table_name
where 1 = 1 and ut.table_name like ('%' || 'XX 表' || '%')
order by ut.table_name asc;

查询表的字段信息

1
2
3
4
5
6
7
8
9
10
11
select
utc.column_name as "字段名",
utc.data_default as column_default as "默认值",
utc.nullable as "是否可为空",
utc.data_type as "类型",
utc.data_length as "长度",
utc.column_id as "序号",
acc.comments as "备注"
from user_tab_columns utc left outer join all_col_comments acc on utc.table_name = acc.table_name and utc.column_name = acc.column_name
where utc.table_name = 'XX 表'
order by utc.column_id asc;