【SQL】如何获得表及字段的说明信息（comment）的创建脚本

写了一个简单实用的的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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-586836/