【SQL】如何獲得表及欄位的說明資訊（comment）的建立指令碼

secooler發表於2009-04-09
SQL指令碼
寫了一個簡單實用的的SQL指令碼，生成資料庫中具體使用者中的註釋資訊（COMMMENT）的建立指令碼。記錄在這裡，方便查詢和參考。
當遷移到測試資料庫後發現註釋資訊均為亂碼後可以使用這個方法快速的恢復。

1.查詢表級別的註釋資訊
select 'COMMENT ON TABLE '||table_name||' IS '''||comments||''';' from user_tab_comments;

2.查詢表中列的註釋資訊
select 'COMMENT ON COLUMN '||table_name||'.'||COLUMN_NAME||' IS '''||COMMENTS||''';' from user_col_comments;

以上提到了兩個檢視，一個是user_tab_comments，另一個是user_col_comments，這兩個檢視分別記錄了表一級別的和行一級別的註釋資訊。
oralce官方文件中是這樣描述的：

ALL_TAB_COMMENTS

ALL_TAB_COMMENTS displays comments on the tables and views accessible to the current user.

Related Views

  • DBA_TAB_COMMENTS displays comments on all tables and views in the database.

  • USER_TAB_COMMENTS displays comments on the tables and views owned by the current user. This view does not display the OWNER column.

Column Datatype NULL Description
OWNER VARCHAR2(30) NOT NULL Owner of the object
TABLE_NAME VARCHAR2(30) NOT NULL Name of the object
TABLE_TYPE VARCHAR2(11)
Type of the object
COMMENTS VARCHAR2(4000)
Comment on the object

ALL_COL_COMMENTS

ALL_COL_COMMENTS displays comments on the columns of the tables and views accessible to the current user.

Related Views

  • DBA_COL_COMMENTS displays comments on the columns of all tables and views in the database.

  • USER_COL_COMMENTS displays comments on the columns of the tables and views owned by the current user. This view does not display the OWNER column.

Column Datatype NULL Description
OWNER VARCHAR2(30) NOT NULL Owner of the object
TABLE_NAME VARCHAR2(30) NOT NULL Name of the object
COLUMN_NAME VARCHAR2(30) NOT NULL Name of the column
COMMENTS VARCHAR2(4000)
Comment on the column


-- The End --

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-586836/，如需轉載，請註明出處，否則將追究法律責任。

相關文章