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

来源:互联网 发布:中美差距 知乎 编辑:程序博客网 时间:2024/03/29 01:52

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

http://hi.baidu.com/%B7%E7%D3%F8/blog/item/db6d0835b7d6af305bb5f5c5.html

 

<%
'使用说明:
'    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""> </td>" & vbcrlf)
Response.write("<td width=""39"" height=""20"" bgcolor=""#FFFFFF""> </td>" & vbcrlf)
Response.write("<td width=""220"" height=""20"" bgcolor=""#FFFFFF""> </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
%>

原创粉丝点击