ORACLE数据库查询表名及表备注 字段名及字段备注SQL

-- 查询指定表里字段及字段注释select column_name,data_type,data_length,DATA_PRECISION ,DATA_SCALEfrom all_tab_columnswhere table_name = UPPER('test11');-- 查询字段及字段注释select COLUMN_NAME,COMMENTSfrom user_col_commentswhere TABLE_NAME = UPPER('test11');-- 查询所有表及表注释select *from user_tab_commentsorder by TABLE_NAME;-- 字段注释select TABLE_NAME,COLUMN_NAME,COMMENTSfrom USER_COL_COMMENTSwhere TABLE_NAME = UPPER('test11');-- 字段注释 合成1列 字段为STRselect COLUMN_NAME || ' ' || COMMENTS STRfrom USER_COL_COMMENTSwhere TABLE_NAME = UPPER('test11')order by COLUMN_NAME;select wmsys.wm_concat(ss) as STRfrom (select 'a.'||tc.COLUMN_NAME||' '||ments ssfrom user_tab_columns tcleft join user_col_comments ccon cc.table_name = tc.TABLE_NAME and cc.column_name = tc.COLUMN_NAMEwhere tc.Table_Name = UPPER('test11')order by tc.column_id asc)t;-- 通过字段注释 查询是否存在想要的字段select cc.column_name,ments,tc.DATA_TYPE,tc.DATA_LENGTHfrom user_tab_columns tcleft join user_col_comments cc on cc.table_name = tc.TABLE_NAME and cc.column_name = tc.COLUMN_NAMEwhere ments like '%债券%';