在oracle中操作表及字段注释
在oracle中操作表及字段注释
在oracle 中创建表如下：
[sql]
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
);
[sql]
-- 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 '是否已发';
备注加好以后，那么如何查询呢？
查询表注释信息如下：
[sql]
SELECT TABLE_NAME, TABLE_TYPE, COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_NAME = 'TM_FAULT_BUS_ATTENTION';
查询表中字段注释信息如下：
[sql]
SELECT TABLE_NAME, COLUMN_NAME, COMMENTS FROM USER_COL_COMMENTS WHERE TABLE_NAME = 'TM_FAULT_BUS_ATTENTION';
查询表中某个字段的注释如下：
[sql]
select comments from USER_COL_COMMENTS where table_name= 'TM_FAULT_BUS_ATTENTION' and column_name= 'LINE_NO'
注意：表名，字段名要大写！
