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;