【Oracle字典表】借助Oracle字典表user_tables和user_tab_comments，一行流取当前用户拥有的表名及表注释

【SQL】

select a.table_name as name,b.comments as remark
from (select table_name from user_tables ) a
inner join (select table_name,comments from user_tab_comments) b
on a.table_name=b.table_name
order by a.table_name

【运行结果】

SQL> select a.table_name as name,b.comments as remark
  2  from (select table_name from user_tables ) a
  3  inner join (select table_name,comments from user_tab_comments) b
  4  on a.table_name=b.table_name
  5  order by a.table_name;

NAME                 REMARK
-------------------- --------------------
DEPT                 部门表
EMP                  雇员表
TEST812              测试表812

【Java程序】

package tbcmts;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test {
    public static void main(String[] args) throws Exception{
        StringBuilder sb=new StringBuilder();
        sb.append(" select a.table_name as name,b.comments as remark");
        sb.append(" from (select table_name from user_tables ) a");
        sb.append(" inner join (select table_name,comments from user_tab_comments) b");
        sb.append(" on a.table_name=b.table_name");
        sb.append(" order by a.table_name");
        final String sql=sb.toString();
        
        try(Connection conn=DbUtil.getConn();
                PreparedStatement ps=conn.prepareStatement(sql);
                ResultSet rs=ps.executeQuery();){
            while(rs.next()) {
                String msg=String.format("表名='%s' 注释='%s'", rs.getString(1),rs.getString(2));
                System.out.println(msg);
            }
        }
    }
    
    
}

【Java程序运行结果】

表名='DEPT' 注释='部门表'
表名='EMP' 注释='雇员表'
表名='TEST812' 注释='测试表812'

END