查询 mysql,oracle 注释

mysql:

 

##查询表中列的注释信息

select * from information_schema.columns

where table_schema = 'db'  #表所在数据库

and table_name = 'tablename'  #你要查的表

 

#只查询列名和注释

select  column_name, column_comment from information_schema.columns where table_schema ='db'  and table_name = 'tablename' 

#查看表的注释

select table_name,table_comment from information_schema.tables  where table_schema = 'db' and table_name ='tablename'

 

Oracle:

查询表字段明细

select column_name,data_type,data_length,DATA_PRECISION ,DATA_SCALE 
from all_tab_columns where table_name='tablename'

字段注释

select   *   from   user_col_comments;

表注释

select   *   from   user_tab_comments;

表明细+表注释+字段明细+字段注释

select ATC.OWNER,atC.TABLE_NAME,utc.comments,ATC.COLUMN_NAME,ATC.DATA_TYPE,ATC.DATA_LENGTH,ATC.NULLABLE, ucc.comments from

(select ATC.OWNER,atC.TABLE_NAME,ATC.COLUMN_NAME,ATC.DATA_TYPE,ATC.DATA_LENGTH,ATC.NULLABLE
from all_tab_columns ATC where ATC.owner in (用戶名1，用戶名2........) ) atc

left outer join user_col_comments ucc on atc.table_name=ucc.table_name and atc.column_name=ucc.column_name

left outer join user_tab_comments utc on atc.table_name=utc.table_name

order by atc.table_name,atc.column_name;