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

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

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"" 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

%>