在oracle中操作表及字段注释

时间:2022-09-21 22:53:18

在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'

注意:表名,字段名要大写!