Asp获取数据库中表名、字段名、字段类型

公司网站准备改版，要整理一下数据库中表的信息，以便改版中创建新表使用。问题是数据库中表太多，总不能一个字段名一个字段名去写，再去写字段类型，这太麻烦了。于是就想使用asp程序把数据库中表名、字段名、字段类型一次读出来，然后自己把页面保存下来，然后再给每个字段加注释，这样就省下一堆时间。以下是我的asp代码，支持ACCESS和SQL Server两种数据库。

<
title>
Asp获取数据库中表名、字段名、字段类型<
/title>
<
style type="text/css">
td
{ font-size:12px
 text-align:center
 }<
/style>
<
%Function OpenConn() '连接数据库代码'On Error Resume NextsqlDatabaseIP = "." '服务器IP，本机直接使用点sqlDatabaseName = "***" '数据库名sqlUserName = "**" '数据库账号sqlUserPass = "***" '密码strConn="Provider=SQLOLEDB
uid="&
sqlUserName&
"
pwd="&
sqlUserPass&
"
Server="&
sqlDatabaseIP&
"
DATABASE="&
sqlDatabaseName&
""'db="db1.mdb"   'Access数据库名'path=server.MapPath(db)    '    strConn = "provider=Microsoft.Jet.OLEDB.4.0
Data Source=" &
 path Set Conn = server.CreateObject("ADODB.Connection")If Err  then  Err.clear  Response.Write("网站访问繁忙，请稍候再访问")  Response.End()End IfConn.Open strConnset OpenConn=ConnEnd FunctionSub CloseConn(Conn) '关闭数据库代码    On Error Resume Next    If IsObject(Conn) then        Conn.Close()        Set Conn = Nothing    End If    If Err Then Err.ClearEnd SubFunction AccessTypeName(num) '获取Access数据库的字段类型    str=""    Select Case num        Case 3            str = "自动编号/数字"        Case 6            str = "货币"        Case 7            str = "日期/时间"        Case 11            str = "是/否"        Case 202            str = "文本"        Case 203            str = "备注/超链接"        Case 205            str = "OLE对象"        End SelectAccessTypeName=strEnd FunctionFunction SqlTypeName(num) '这是获取sql数据库的字段类型str=""    Select Case num        Case 2            str = "smallint"        Case 3            str = "int"        Case 4            str = "real"        Case 5            str = "float"        Case 6            str = "money/smallmoney"        Case 11            str = "bit"        Case 12            str = "sql_variant"        Case 17            str = "tinyint"        Case 20            str = "bigint"        Case 72            str = "uniqueidentifier"        Case 128            str = "binary/timestamp"        Case 129            str = "char"        Case 130            str = "nchar"        Case 131            str = "decimal/numeric"        Case 135            str = "datetime/smalldatetime"        Case 200            str = "varchar"        Case 201            str = "text"        Case 202            str = "nvarchar"        Case 203            str = "ntext"        Case 204            str = "varbinary"        Case 205            str = "image"        End SelectSqlTypeName=strEnd Function'功能：Asp获取数据库中表名、字段名、字段类型'作者：wangsdong'来源：www.K88.NET'set Conn=openconn()set rs=server.CreateObject("adodb.recordset")Set rs=Conn.OpenSchema(20) Do Until rs.EOF If rs(3)="TABLE" Thenresponse.write "表名:"&
rs(2)&
"<
br />
"Set rs1=server.CreateObject("adodb.recordset")sql="select * from ["&
rs(2)&
"]"Set rs1=conn.execute(sql)response.write "<
table cellpadding=0 cellspacing=0 border=1 width=""500"" >
<
tr>
<
td>
字段名<
/td>
<
td>
字段类型<
/td>
<
td>
备注<
/td>
<
/tr>
"For i=0 To rs1.fields.count-1fieldname=rs1.fields(i).namefieldtype=rs1.fields(i).typeresponse.write "<
tr>
<
td>
"&
fieldname&
"<
/td>
<
td>
"&
AccessTypeName(fieldtype)&
"&
nbsp
<
/td>
<
td>
&
nbsp
<
/td>
<
/tr>
"  'Access数据库'response.write "<
tr>
<
td>
"&
fieldname&
"<
/td>
<
td>
"&
SqlTypeName(fieldtype)&
"&
nbsp
<
/td>
<
td>
&
nbsp
<
/td>
<
/tr>
" 'sql数据库使用这句Nextresponse.write "<
/table>
<
br>
<
br>
"End Ifrs.MoveNext Loop Set rs=NothingSet conn=nothing%>