Oracle Comment添加表备注和列备注添加和查询comment on table or culumn

秒速五厘米 2022-04-05 12:22 737阅读 0赞

<table> 
 <tbody> 
 <tr> 
 <td> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;<a href="http://www.itpub.net/tree/index_1/" rel="nofollow">oracle</a>开发过程中，经常会用到comment on table is 和comment on column is来添加备注，以便与查看表和列的作用。 &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;添加标注备注的方法如下 
 <ol> 
 <li>&nbsp; &nbsp; --添加表备注&nbsp; &nbsp;</li> 
 <li>&nbsp; &nbsp; COMMENT ON TABLE b_so IS '发货订单';</li> 
 </ol>复制代码&nbsp; &nbsp;&nbsp; &nbsp; 添加列备注 
 <ol> 
 <li>&nbsp; &nbsp; --添加列注释&nbsp; &nbsp;&nbsp;</li> 
 <li>COMMENT ON column b_so.c_customerup_id IS '上级经销商';&nbsp;</li> 
 <li>&nbsp;</li> 
 </ol>复制代码&nbsp; &nbsp;&nbsp; &nbsp; 查询表备注&nbsp; 
 <ol> 
 <li>--查询表备注&nbsp;</li> 
 <li>SELECT * FROM user_tab_comments WHERE comments IS NOT NULL;</li> 
 </ol>复制代码&nbsp; &nbsp;&nbsp; &nbsp; 查询效果如下：&nbsp; &nbsp;&nbsp; &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;<img alt="" src="https://img-blog.csdn.net/20141028110721314?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvamluYXF1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center">&nbsp; &nbsp;&nbsp; &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;查询列备注&nbsp; &nbsp;&nbsp; 
 <ol> 
 <li>--查询列备注&nbsp; &nbsp;&nbsp;</li> 
 <li>SELECT * FROM user_col_comments WHERE comments IS NOT NULL;</li> 
 </ol>复制代码&nbsp; &nbsp;&nbsp; &nbsp; 列备注查询结果如下： &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;<img alt="" src="https://img-blog.csdn.net/20141028111020718?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvamluYXF1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center"> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;oracle数据库中，表备注和列备注对应的视图为user_tab_comments和user_col_comments;详细代码如下： &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;表备注视图： 
 <ol> 
 <li>&nbsp;</li> 
 <li>create or replace view sys.user_tab_comments as</li> 
 <li>select o.name,</li> 
 <li>&nbsp; &nbsp;&nbsp; &nbsp; decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',</li> 
 <li>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; 4, 'VIEW', 5, 'SYNONYM', 'UNDEFINED'),</li> 
 <li>&nbsp; &nbsp;&nbsp; &nbsp; c.comment$</li> 
 <li>from sys."_CURRENT_EDITION_OBJ" o, sys.com$ c</li> 
 <li>where o.owner# = userenv('SCHEMAID')</li> 
 <li>&nbsp;&nbsp;and&nbsp;<a href="http://www.itpub.net/tree/index_339/" rel="nofollow">bi</a>tand(o.flags,128) = 0</li> 
 <li>&nbsp;&nbsp;and (o.type# in (4)&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;/* view */</li> 
 <li>&nbsp; &nbsp;&nbsp; &nbsp; or</li> 
 <li>&nbsp; &nbsp;&nbsp; &nbsp; (o.type# = 2&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;/* tables */</li> 
 <li>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;AND&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;/* excluding iot-overflow, nested or mv container tables */</li> 
 <li>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;not exists (select null</li> 
 <li>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; from sys.tab$ t</li> 
 <li>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;where t.obj# = o.obj#</li> 
 <li>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;and (bitand(t.property, 512) = 512 or</li> 
 <li>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; bitand(t.property, 8192) = 8192 OR</li> 
 <li>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; bitand(t.property, 67108864) = 67108864))))</li> 
 <li>&nbsp;&nbsp;and o.obj# = c.obj#(+)</li> 
 <li>&nbsp;&nbsp;and c.col#(+) is null;</li> 
 <li>comment on column sys.USER_TAB_COMMENTS.TABLE_NAME is 'Name of the object';</li> 
 <li>comment on column sys.USER_TAB_COMMENTS.TABLE_TYPE is 'Type of the object:&nbsp;&nbsp;"TABLE" or "VIEW"';</li> 
 <li>comment on column sys.USER_TAB_COMMENTS.COMMENTS is 'Comment on the object';</li> 
 <li>&nbsp;</li> 
 </ol>复制代码 &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;列备注视图： 
 <ol> 
 <li>create or replace view sys.user_col_comments as</li> 
 <li>select o.name, c.name, co.comment$</li> 
 <li>from sys."_CURRENT_EDITION_OBJ" o, sys.col$ c, sys.com$ co</li> 
 <li>where o.owner# = userenv('SCHEMAID')</li> 
 <li>&nbsp;&nbsp;and o.type# in (2, 4)</li> 
 <li>&nbsp;&nbsp;and o.obj# = c.obj#</li> 
 <li>&nbsp;&nbsp;and c.obj# = co.obj#(+)</li> 
 <li>&nbsp;&nbsp;and c.intcol# = co.col#(+)</li> 
 <li>&nbsp;&nbsp;and bitand(c.property, 32) = 0 /* not hidden column */;</li> 
 <li>comment on column sys.USER_COL_COMMENTS.TABLE_NAME is 'Object name';</li> 
 <li>comment on column sys.USER_COL_COMMENTS.COLUMN_NAME is 'Column name';</li> 
 <li>comment on column sys.USER_COL_COMMENTS.COMMENTS is 'Comment on the column';</li> 
 </ol>复制代码 &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;oracle数据库查询备注的常用方法： **************oracle表备注查询(comment on table)************** &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;通过表名查询表备注&nbsp; &nbsp;&nbsp;&nbsp; 
 <ol> 
 <li>--通过表名查询表&nbsp; &nbsp;&nbsp;</li> 
 <li>SELECT *&nbsp; &nbsp; FROM user_tab_comments&nbsp; &nbsp; WHERE comments IS NOT NULL&nbsp; &nbsp; AND table_name = upper('v_fa_customer')&nbsp; &nbsp; AND rownum = 1;</li> 
 </ol>复制代码 &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;通过备注查表名&nbsp;&nbsp; 
 <ol> 
 <li>--通过备注查表名&nbsp;</li> 
 <li>&nbsp; &nbsp;SELECT *&nbsp; &nbsp; FROM user_tab_comments&nbsp; &nbsp; WHERE comments = '跨级销售单';</li> 
 </ol>复制代码 &nbsp;&nbsp; &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;通过表类型查备注&nbsp; &nbsp; 
 <ol> 
 <li>--通过表类型查表备注&nbsp; &nbsp;&nbsp;</li> 
 <li>SELECT DISTINCT table_type FROM user_tab_comments WHERE table_type=UPPER('table');</li> 
 <li>&nbsp;</li> 
 <li>--通过表类型查表备注&nbsp; &nbsp;&nbsp;</li> 
 <li>SELECT DISTINCT table_type FROM user_tab_comments WHERE table_type=UPPER('view');</li> 
 <li>&nbsp;</li> 
 </ol>复制代码 **************oracle列备注查询(comments column)************** &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;通过列名查列备注 
 <ol> 
 <li>--通过列名查列备注&nbsp; &nbsp;&nbsp;</li> 
 <li>SELECT *&nbsp; &nbsp; FROM user_col_comments&nbsp; &nbsp; WHERE column_name = upper('c_customerup_id')&nbsp; &nbsp; AND comments IS NOT NULL;</li> 
 </ol>复制代码 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;通过表名查备注 
 <ol> 
 <li>--通过表名查备注&nbsp;</li> 
 <li>&nbsp; &nbsp;SELECT *&nbsp; &nbsp; FROM user_col_comments&nbsp; &nbsp; AND comments IS NOT NULL&nbsp; &nbsp; AND table_name = upper('b_so');</li> 
 </ol>复制代码 &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;通过列名查备注&nbsp; &nbsp;&nbsp; 
 <ol> 
 <li>--通过列名查备注</li> 
 <li>SELECT *&nbsp; &nbsp; FROM user_col_comments&nbsp; &nbsp; WHERE comments IS NOT NULL&nbsp; &nbsp; AND column_name =UPPER( 'docno');</li> 
 </ol>复制代码 &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;通过备注查列名和表名 
 <ol> 
 <li>&nbsp; &nbsp; --通过备注查列名和表名&nbsp;</li> 
 <li>&nbsp; &nbsp;SELECT *&nbsp; &nbsp; FROM user_col_comments&nbsp; &nbsp; WHERE comments = '单据类型';</li> 
 </ol>复制代码 &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;这里是非常简单的笔记，如果大家喜欢，欢迎收藏转载，如有疑问，请留言我哦，嘿嘿，祝学习愉快！ &nbsp;</td> 
 </tr> 
 </tbody> 
</table>

[oracle][], [COMMENT][], [查询][Link 1], [TABLE][], [开发][Link 2]

[oracle]: http://www.itpub.net/misc.php?mod=tag&id=52
[COMMENT]: http://www.itpub.net/misc.php?mod=tag&id=11330
[Link 1]: http://www.itpub.net/misc.php?mod=tag&id=3623
[TABLE]: http://www.itpub.net/misc.php?mod=tag&id=8216
[Link 2]: http://www.itpub.net/misc.php?mod=tag&id=310