/**
*
*http://blog.csdn.net/matol/article/details/5950459
*
**/
<%--
* <p>Copyright: Copyright (c) 2010</p>
* <p>Company: matol</p>
* 查看Oracle数据库当前连接的用户下的所有表结构、视图、索引
* @author matol
* @version 1.0
* Date: 2010-10-18
--%>
<%@ 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"%>
<%
try{
request.setCharacterEncoding("GBK");
String querySql = "";
Statement cn = dBConn.dBConnect(); //根据情况自己导入连接对象
ResultSet rs = null;
String DBUserName = "";
rs = cn.executeQuery("select user from dual");
if(rs.next()){
DBUserName = StringPlus.getString(rs.getString(1));
}
rs.close();
%>
<html>
<head>
<meta http-equiv="Content-Language" content="zh-cn">
<meta http-equiv="Content-Type" content="text/html; charset=GBK">
<title>Oracle数据库当前连接的用户下的所有表结构、视图、索引</title>
</head>
<body>
<center>
<table border="0" cellpadding="2" cellspacing="1" style="border-collapse: collapse" mce_style="border-collapse: collapse" width="650" id="table1">
<tr>
<td height="20" class="pertainFont">一、系统所有表</td>
</tr>
<tr>
<td height="20" align="center">
<table border="1" cellpadding="2" cellspacing="1" style="border-collapse: collapse" mce_style="border-collapse: collapse" width="100%" id="table2">
<tr>
<td align="center" width="40" height="20" class="DarkBlueBG">序号</td>
<td align="center" width="282" height="20" class="DarkBlueBG">表名称</td>
<td align="center" height="20" class="DarkBlueBG">注解</td>
</tr>
<%
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] = StringPlus.getString(rs.getString("TABLE_NAME"));
comments[tableSuffix] = StringPlus.getString(rs.getString("COMMENTS"));
%>
<tr>
<td width="40" height="20" align="center"><%=StringPlus.getString(rs.getString("rownum"))%></td>
<td width="282" height="20"><a href="#<%=tableName[tableSuffix]%>"><%=tableName[tableSuffix]%></a></td>
<td height="20"><a href="#<%=tableName[tableSuffix]%>"><%=comments[tableSuffix]%></a></td>
</tr>
<%
tableSuffix++;
}
rs.close();
%>
</table>
</td>
</tr>
<tr>
<td height="20" class="pertainFont">二、系统视图</td>
</tr>
<tr>
<td height="20" align="center">
<table border="1" cellpadding="2" cellspacing="1" style="border-collapse: collapse" mce_style="border-collapse: collapse" width="100%" id="table2">
<tr>
<td align="center" width="40" height="20" class="DarkBlueBG">序号</td>
<td align="center" width="282" height="20" class="DarkBlueBG">视图名称</td>
<td align="center" height="20" class="DarkBlueBG">注解</td>
</tr>
<%
querySql = "select t.*, rownum from all_tab_comments t where t.owner='" + DBUserName + "' and t.table_type='VIEW' order by table_name";
rs = cn.executeQuery(querySql);
while(rs.next()){
%>
<tr>
<td width="40" height="20" align="center"><%=StringPlus.getString(rs.getString("rownum"))%></td>
<td width="282" height="20"><%=StringPlus.getString(rs.getString("TABLE_NAME"))%></td>
<td height="20"><%=StringPlus.getString(rs.getString("COMMENTS"))%></td>
</tr>
<%
}
rs.close();
%>
</table>
</td>
</tr>
<tr>
<td height="20" class="pertainFont">四、索引</td>
</tr>
<tr>
<td height="20" align="center">
<table border="1" cellpadding="2" cellspacing="1" style="border-collapse: collapse" mce_style="border-collapse: collapse" width="100%" id="table2">
<tr>
<td align="center" width="40" height="20" class="DarkBlueBG">序号</td>
<td align="center" width="282" height="20" class="DarkBlueBG">索引名称</td>
<td align="center" height="20" class="DarkBlueBG">表名称</td>
</tr>
<%
querySql = "select t.*, rownum from all_indexes t where t.table_owner='" + DBUserName + "' order by T.table_name";
rs = cn.executeQuery(querySql);
while(rs.next()){
%>
<tr>
<td width="40" height="20" align="center"><%=StringPlus.getString(rs.getString("rownum"))%></td>
<td width="282" height="20"><%=StringPlus.getString(rs.getString("INDEX_NAME"))%></td>
<td height="20"><%=StringPlus.getString(rs.getString("TABLE_NAME"))%></td>
</tr>
<%
}
rs.close();
%>
</table>
</td>
</tr>
<tr>
<td height="20" align="left" class="pertainFont">四、表结构</td>
</tr>
<%
for(int i=0;i<tableNum;i++){
%>
<tr>
<td height="20" align="left"><a name="<%=tableName[i]%>"><b><%=i+1%>、<%=tableName[i]%>（<%=comments[i]%>）</b></a></td>
</tr>
<tr>
<td height="20" align="center">
<table border="1" cellpadding="2" cellspacing="1" style="border-collapse: collapse" mce_style="border-collapse: collapse" width="100%" id="table2">
<tr>
<td align="center" width="40" height="20" class="DarkBlueBG">序号</td>
<td align="center" width="171" height="20" class="DarkBlueBG">字段名称</td>
<td align="center" height="20" class="DarkBlueBG">注解</td>
<td align="center" height="20" class="DarkBlueBG" width="112">字段类型(宽度)</td>
<td align="center" height="20" class="DarkBlueBG" width="31"> </td>
<td align="center" height="20" class="DarkBlueBG" width="31"> </td>
<td align="center" height="20" class="DarkBlueBG" width="31"> </td>
</tr>
<%
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] + "'";
rs = cn.executeQuery(querySql);
while(rs.next()){
%>
<tr>
<td width="40" height="20" align="center"><%=StringPlus.getString(rs.getString("rownum"))%></td>
<td width="171" height="20"><%=StringPlus.getString(rs.getString("column_name"))%></td>
<td height="20"><%=StringPlus.getString(rs.getString("COMMENTS"))%></td>
<td height="20" width="112" align="left"><%=StringPlus.getString(rs.getString("data_type"))%>(<%=StringPlus.getString(rs.getString("data_length"))%>)</td>
<td height="20" width="31" align="center">FALSE</td>
<td height="20" width="31" align="center">FALSE</td>
<td height="20" width="31" align="center">FALSE</td>
</tr>
<%
}
rs.close();
%>
</table>
</td>
</tr>
<%
}
%>
</table>
</center>
</body>
</html>
<%
}
catch(Exception e){
out.print(e.getMessage());
}
finally{
dBConn.close();
}
%>
浙公网安备 33010602011771号