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;