木子屋
 

标题: 获取数据库所有表及其字段名称、类型、长度

作者: dnawo

日期: 2008-01-15 04:34 PM

分类: Web编程

内容: 复制内容到剪贴板 程序代码<%
'使用说明：
'    1.配置数据库类型，见IsSqlDataBase
'    2.配置数据库名、帐号、密码等参数值，见conn.connectionString
'
Const IsSqlDataBase = 1 '数据库类型  1为SQL数据库，0为Access数据库
Dim conn,rs,rs2,sqlstr,t_count,table_name,i
'On error resume next
Set conn = Server.CreateObject("ADODB.Connection")
If IsSqlDataBase = 0 Then
    conn.connectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & Server.MapPath("db1.mdb")
Else
    conn.connectionString = "Provider=SQLOLEDB;Data Source=192.168.1.111;Initial Catalog=union_5k3g;User ID=sa;Password=sa"
End If
Conn.open
If err then
  Response.write "请检查数据库路径!"
  err.clear
  Response.End
End if
T_count = 0
Set rs = Conn.openschema(20)
Do while not rs.eof
  If rs("table_type")="TABLE" then
    T_count = T_count + 1
    table_name = rs("table_name")
    Response.write T_count & ".表名(" & table_name & ")：<br>"
    Sqlstr = "select * from " & table_name
    Set rs2 = Server.CreateObject("ADODB.RecordSet")
    Rs2.open sqlstr,conn,0,1
    Response.write("<table width=""600"" height=""39"" border=""0"" cellpadding=""0"" cellspacing=""1"" bgcolor=""#000000"">" & vbcrlf)
    Response.write("<tr>" & vbcrlf)
    Response.write("<td width=""117"" height=""20"" bgcolor=""#FFFFFF""><strong>字段名</strong></td>" & vbcrlf)
    Response.write("<td width=""83"" height=""20"" bgcolor=""#FFFFFF""><strong>类型</strong></td>" & vbcrlf)
    Response.write("<td width=""47"" height=""20"" bgcolor=""#FFFFFF""><strong>长度</strong></td>" & vbcrlf)
    Response.write("<td width=""94"" height=""20"" bgcolor=""#FFFFFF""><strong>默认值</strong></td>" & vbcrlf)
    Response.write("<td width=""39"" height=""20"" bgcolor=""#FFFFFF""><strong>主键</strong></td>" & vbcrlf)
    Response.write("<td width=""220"" height=""20"" bgcolor=""#FFFFFF""><strong>说明</strong></td>" & vbcrlf)
    Response.write("</tr>" & vbcrlf)
    For i=0 to rs2.fields.count-1
        Response.write("<tr>" & vbcrlf)
        Response.write("<td width=""117"" height=""20"" bgcolor=""#FFFFFF"">" & rs2.fields.item(i).name & "</td>" & vbcrlf)
        If IsSqlDataBase = 0 Then
            Response.write("<td width=""83"" height=""20"" bgcolor=""#FFFFFF"">" & AccessTypeName(rs2.fields.item(i).type) & "</td>" & vbcrlf)
        Else
            Response.write("<td width=""83"" height=""20"" bgcolor=""#FFFFFF"">" & SqlTypeName(rs2.fields.item(i).type) & "</td>" & vbcrlf)
        End If
        Response.write("<td width=""47"" height=""20"" bgcolor=""#FFFFFF"">" & rs2.fields.item(i).Definedsize & "</td>" & vbcrlf)
        Response.write("<td width=""94"" height=""20"" bgcolor=""#FFFFFF"">&nbsp;</td>" & vbcrlf)
        Response.write("<td width=""39"" height=""20"" bgcolor=""#FFFFFF"">&nbsp;</td>" & vbcrlf)
        Response.write("<td width=""220"" height=""20"" bgcolor=""#FFFFFF"">&nbsp;</td>" & vbcrlf)
        Response.write("</tr>" & vbcrlf)
    Next
    Response.write("</table><p>")
    Rs2.close
    Set rs2 = nothing
  End if
  Rs.movenext
Loop
Response.write "<u><b>总计</b><font color=red>" & t_count & "</font><b>个表</b></u>"
Response.write "<br/><br/>说明:字段类型中带有""/""的表示可能为两者之一,需进一步确定;Definedsize在获取text、ntext等类型字段长度时不准确！"
Rs.close
Set rs = nothing
Conn.close
Set conn=nothing
'SQL Server字段类型转换
Function SqlTypeName(num)
    Select Case num
        Case 2
            SqlTypeName = "smallint"
        Case 3
            SqlTypeName = "int"
        Case 4
            SqlTypeName = "real"
        Case 5
            SqlTypeName = "float"
        Case 6
            SqlTypeName = "money/smallmoney"
        Case 11
            SqlTypeName = "bit"
        Case 12
            SqlTypeName = "sql_variant"
        Case 17
            SqlTypeName = "tinyint"
        Case 20
            SqlTypeName = "bigint"
        Case 72
            SqlTypeName = "uniqueidentifier"
        Case 128
            SqlTypeName = "binary/timestamp"
        Case 129
            SqlTypeName = "char"
        Case 130
            SqlTypeName = "nchar"
        Case 131
            SqlTypeName = "decimal/numeric"
        Case 135
            SqlTypeName = "datetime/smalldatetime"
        Case 200
            SqlTypeName = "varchar"
        Case 201
            SqlTypeName = "text"
        Case 202
            SqlTypeName = "nvarchar"
        Case 203
            SqlTypeName = "ntext"
        Case 204
            SqlTypeName = "varbinary"
        Case 205
            SqlTypeName = "image"
        End Select
End Function
'Access字段类型转换
Function AccessTypeName(num)
    Select Case num
        Case 3
            AccessTypeName = "自动编号/数字"
        Case 6
            AccessTypeName = "货币"
        Case 7
            AccessTypeName = "日期/时间"
        Case 11
            AccessTypeName = "是/否"
        Case 202
            AccessTypeName = "文本"
        Case 203
            AccessTypeName = "备注/超链接"
        Case 205
            AccessTypeName = "OLE对象"
        End Select
End Function
%>

+ 查看当前日志评论 (0)

 

登录

────────────

木子屋

PJBlog3 v3.2.9.518 Inside.

Processed In 0.609 ms

标题: 获取数据库所有表及其字段名称、类型、长度

你没有权限发表评论

────────────

木子屋

PJBlog3 v3.2.9.518 Inside.

Processed In 0.609 ms

暂无评论

返回

────────────

木子屋

PJBlog3 v3.2.9.518 Inside.

Processed In 0.609 ms