--查询所有用户表 表结构及注释
select t.TABLE_NAME,
utc.comments,
c.COLUMN_NAME,
ucc.comments,
c.DATA_TYPE,
c.DATA_LENGTH,
c.NULLABLE,
case when ( (select count(*) from user_constraints ucs inner join user_cons_columns uccs on uccs.constraint_name=ucs.constraint_name where ucs.table_name = c.TABLE_NAME and uccs.column_name = c.COLUMN_NAME
and ucs.constraint_type = 'P' )>0) then 'Y' else 'N' end is_pk
from user_tables t
inner join user_tab_columns c
on t.TABLE_NAME = c.TABLE_NAME
left join user_tab_comments utc on utc.table_name = t.TABLE_NAME
left join user_col_comments ucc on ucc.column_name = c.COLUMN_NAME and ucc.table_name = c.TABLE_NAME
order by t.TABLE_NAME,c.COLUMN_NAME
--查询所有用户表注释
SELECT t.TABLE_NAME, utc.comments FROM user_tables T left join user_tab_comments utc on utc.table_name = t.TABLE_NAME