Oracle 下读取表/字段的备注信息

Oracle 通过COMMENT ON TABLE  /  COMMENT ON COLUMN 追加表/字段的备注。

 CREATE TABLE "MR_DEPT" (

       "DEPT_ID" NUMBER NOT NULL ,

       "PARENT_ID" NUMBER,

       "DEPT_NAME" CHAR(20) NOT NULL ,

       "STATUS" NUMBER DEFAULT 1 NOT NULL ,

PRIMARY KEY ("DEPT_ID")

);

 

COMMENT ON TABLE "MR_DEPT" IS '部门表';

COMMENT ON COLUMN "MR_DEPT"."DEPT_ID" IS '部门编号';

COMMENT ON COLUMN "MR_DEPT"."PARENT_ID" IS '上级部门编号';

COMMENT ON COLUMN "MR_DEPT"."DEPT_NAME" IS '部门名';

COMMENT ON COLUMN "MR_DEPT"."STATUS" IS '状态';

 

备注加好以后，如何在查询中检索呢？

 

查询表的备注信息

SELECT

  TABLE_NAME,

  TABLE_TYPE,

  COMMENTS

FROM

  USER_TAB_COMMENTS

WHERE

  TABLE_NAME = 'MR_DEPT';

 

 

查询字段的备注信息

SELECT

  TABLE_NAME,

  COLUMN_NAME,

  COMMENTS

FROM

  USER_COL_COMMENTS

WHERE

  TABLE_NAME = 'MR_DEPT';