Oracle数据库当前连接的用户下的所有表结构、视图、索引

<%--
 * 
Copyright: Copyright (c) 2013
 * 
Company: matol
 * 查看Oracle数据库当前连接的用户下的所有表结构、视图、索引
 * @author matol
 * @version 2.0
 * Date: 2013-08-25
--%>
<%@ page contentType="text/html; charset=GBK" %>
<%@ page language="java" import="java.sql.*"%>
<%@ page import="java.util.*" %>
<%@ page import="java.text.*"%>
<%@ page import="java.util.Date"%>

<%
Connection con = null;
try{
 request.setCharacterEncoding("GBK");
 
 String dbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";    
 String theUser = "matol";    
 String thePw = "matol";    
  
 Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();    
 con = DriverManager.getConnection(dbUrl, theUser, thePw);   
 
 
 String querySql = "";
 Statement cn = con.createStatement(); 
 ResultSet rs = null;
 String DBUserName = "";

 rs = cn.executeQuery("select user from dual");
 if(rs.next()){
  DBUserName = rs.getString(1);
 }
 rs.close();
%>



 
  
  
 
  
  
 
	 





  
  

   
   
 
   
   
   
   
一、系统表结构
 
       
序号
表名称
注解
 <% int tableNum = 0; int tableSuffix = 0; querySql = "select count(*) from all_tab_comments t where t.owner='" + DBUserName + "' and t.table_type='TABLE'"; rs = cn.executeQuery(querySql); rs.next(); tableNum = rs.getInt(1); rs.close(); String tableName[] = new String[tableNum]; String comments[] = new String[tableNum]; querySql = "select t.*, rownum from all_tab_comments t where t.owner='" + DBUserName + "' and t.table_type='TABLE' order by table_name"; rs = cn.executeQuery(querySql); while(rs.next()){ tableName[tableSuffix] = rs.getString("TABLE_NAME"); comments[tableSuffix] = rs.getString("COMMENTS"); %> 
         
<%=rs.getString("rownum")%>
<%=tableName[tableSuffix]%>
<%=comments[tableSuffix]%>
 <% tableSuffix++; } rs.close(); %> 
         
        
 
     
     
 
  
     
     
二、用户表结构
<%
 for(int i=0;i
     
     
<%=i+1%>、<%=tableName[i]%>（<%=comments[i]%>）
 
       
序号
字段名称
字段类型(宽度)
是否为空
注解
　
　
 <% querySql = "select t.column_name, t.COMMENTS, t1.data_type, t1.data_length, t1.nullable, rownum from all_col_comments t, all_tab_cols t1 where t.column_name = t1.column_name and t.table_name = t1.table_name and t1.owner='" + DBUserName + "' and t.owner = t1.owner and t.TABLE_NAME = '" + tableName[i] + "' order by t1.COLUMN_ID "; rs = cn.executeQuery(querySql); while(rs.next()){ %> 
         
<%=rs.getString("rownum")%>
<%=rs.getString("column_name")%>
<%=rs.getString("data_type")%>(<%=rs.getString("data_length")%>)
<%=rs.getString("nullable")%>
<%=rs.getString("COMMENTS")%>
FALSE
FALSE
 <% } rs.close(); %> 
         
        
 
     
     
 <% 
 }
%>

 
     
     
三、视图
 
       
序号
视图名称
注解
 <% querySql = "select t.*, rownum from all_tab_comments t where t.owner='" + DBUserName + "' and t.table_type='VIEW' order by rownum desc "; rs = cn.executeQuery(querySql); while(rs.next()){ %> 
         
<%=rs.getString("rownum")%>
<%=rs.getString("TABLE_NAME")%>
<%=rs.getString("COMMENTS")%>
 <% } rs.close(); %> 
         
        
 
     
     
 
 
 
     
     
四、索引
 
       
序号
索引名称
表名称
 <% querySql = "select t.*, rownum from all_indexes t where t.table_owner='" + DBUserName + "' order by rownum desc "; rs = cn.executeQuery(querySql); while(rs.next()){ %> 
         
<%=rs.getString("rownum")%>
<%=rs.getString("INDEX_NAME")%>
<%=rs.getString("TABLE_NAME")%>
 <% } rs.close(); %> 
         
        
 
     
     



    
    
   
   

  
  

<%
}
catch(Exception e){
 out.print(e.getMessage());
}
finally{
 con.close();
}
%>