oracle数据库查询所有表名和注释等

目录

• 查询库里所有表名和注释
• 查询所有表名
• dba_tables、all_tables、user_tables区别
• 附：表字段注释拼接
• 总结

-- 模式名大写
SELECT TABLE_NAME,COMMENTS from all_tab_comments where OWNER='模式名';

-- 模式名大写
SELECT * FROM all_tables WHERE OWNER = '模式名';
 
-- 加 ORDER BY TABLE_NAME 是让结果按照表名顺序展示
SELECT * FROM all_tables WHERE OWNER = '模式名' ORDER BY TABLE_NAME;

SELECT * FROM user_tables
-- 加 ORDER BY TABLE_NAME 是让结果按照表名顺序展示
SELECT * FROM user_tables ORDER BY TABLE_NAME

select * from user_tables where table_name = upper('ali_org');

select 
'comment on column '|| table_name ||'.'||column_name||' is '||''''||comments||''';'
from all_col_comments where table_name='TABLE_NAME' and owner = 'OWNER_NAME';

select 
'comment on table '|| table_name ||' is '||''''||comments||''';'
from all_tab_comments where table_name='TABLE_NAME' and owner = 'OWNER_NAME';