【oracle sql】一行流取Oracle某表的字段名、注释和排序号

【sql】

select a.column_name as name,a.column_id as order_num,b.comments as remark
from (select column_name,column_id from user_tab_columns where table_name=upper('test812')) a
left join (select column_name,comments from user_col_comments where table_name=upper('test812')) b
on a.column_name=b.column_name
order by a.column_id

【相对优势】

用Metadata去取表字段信息，对oracle数据库稍有别扭，这种利用字典表的方法更简单快捷。

【实验过程】

1.建表并加注释

create table test812(
    id number(12),
    name nvarchar2(20),
    title varchar2(20),
    primary key(id)
);

comment on table test812 is '测试表812';
comment on column  test812.id  is '序列号';
comment on column  test812.name is '名称';
comment on column  test812.title is '职称';

2.查询

SQL> select a.column_name as name,a.column_id as order_num,b.comments as remark
  2  from (select column_name,column_id from user_tab_columns where table_name=upper('test812')) a
  3  left join (select column_name,comments from user_col_comments where table_name=upper('test812')) b
  4  on a.column_name=b.column_name
  5  order by a.column_id;

NAME                  ORDER_NUM REMARK
-------------------- ---------- --------------------
ID                            1 序列号
NAME                          2 名称
TITLE                         3 职称

END