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

-- 查询指定表里字段及字段注释
select column_name,data_type,data_length,DATA_PRECISION ,DATA_SCALE
from all_tab_columns
where table_name = UPPER('test11');

-- 查询字段及字段注释
select COLUMN_NAME,COMMENTS
from user_col_comments
where TABLE_NAME = UPPER('test11');

-- 查询所有表及表注释
select *
from user_tab_comments
order by TABLE_NAME;

-- 字段注释
select TABLE_NAME,COLUMN_NAME,COMMENTS
from USER_COL_COMMENTS
where TABLE_NAME = UPPER('test11');

-- 字段注释 合成1列 字段为STR
select COLUMN_NAME || ' ' || COMMENTS STR
from USER_COL_COMMENTS
where TABLE_NAME = UPPER('test11')
order by COLUMN_NAME;

select wmsys.wm_concat(ss) as STR
from (
    select 'a.'||tc.COLUMN_NAME||' '||cc.comments ss
    from user_tab_columns tc
    left join user_col_comments cc
    on cc.table_name = tc.TABLE_NAME and cc.column_name = tc.COLUMN_NAME
where tc.Table_Name = UPPER('test11')
order by tc.column_id asc)t;

-- 通过字段注释 查询是否存在想要的字段
select cc.column_name,cc.comments,tc.DATA_TYPE,tc.DATA_LENGTH
from user_tab_columns tc
left join user_col_comments cc on cc.table_name = tc.TABLE_NAME and cc.column_name = tc.COLUMN_NAME
where cc.comments like '%债券%';