在oracle中操作表及字段注释

柔光的暖阳◎ 2022-02-22 12:54 251阅读 0赞

在oracle 中创建表如下：

create table TM_FAULT_BUS_ATTENTION
    (
      BUS_NO     NUMBER(6),
      LINE_NO    VARCHAR2(6),
      FILA_NO    NUMBER(3),
      BUS_JOB_NO VARCHAR2(20) not null,
      CITY_NO    NUMBER(4),
      GROUP_NO   NUMBER(4),
      INS_TIME   DATE,
      IS_SEND    NUMBER(1) default 1
    );

-- Add comments to the table 
    comment on table TM_FAULT_BUS_ATTENTION
      is '添加故障车辆信息表';
    -- Add comments to the columns 
    comment on column TM_FAULT_BUS_ATTENTION.BUS_NO
      is '车号';
    comment on column TM_FAULT_BUS_ATTENTION.LINE_NO
      is '线路号';
    comment on column TM_FAULT_BUS_ATTENTION.FILA_NO
      is '公司号';
    comment on column TM_FAULT_BUS_ATTENTION.BUS_JOB_NO
      is '车工号';
    comment on column TM_FAULT_BUS_ATTENTION.CITY_NO
      is '地区';
    comment on column TM_FAULT_BUS_ATTENTION.GROUP_NO
      is '车队';
    comment on column TM_FAULT_BUS_ATTENTION.INS_TIME
      is '下载时间';
    comment on column TM_FAULT_BUS_ATTENTION.IS_SEND
      is '是否已发';

备注加好以后，那么如何查询呢？

查询表注释信息如下：

SELECT  TABLE_NAME,  TABLE_TYPE,  COMMENTS FROM  USER_TAB_COMMENTS WHERE  TABLE_NAME = 'TM_FAULT_BUS_ATTENTION';查询表中字段注释信息如下：

SELECT  TABLE_NAME,  COLUMN_NAME,  COMMENTS FROM  USER_COL_COMMENTS WHERE  TABLE_NAME = 'TM_FAULT_BUS_ATTENTION'; 查询表中某个字段的注释如下： select  comments  from USER_COL_COMMENTS where table_name= 'TM_FAULT_BUS_ATTENTION' and column_name= 'LINE_NO'
    注意：表名，字段名要大写！