Get all tables of the database and their field names, types, length __ Database

Get all tables of the database and their field names, types, lengths

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

<%
' Use instructions:
' 1. Configure database type, see Issqldatabase
' 2. Configure database name, account number, password and other parameter values, see conn.connectionstring
'

Const issqldatabase = 1 ' database type 1 is SQL database, 0 is an Access database

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 "Please check the database path!"
Err.Clear
Response.End
End If
T_count = 0
Set rs = Conn.openschema (20)
Do as 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 (" & table_name & "):<br>"
SQLSTR = "SELECT * FROM" & table_name
Set rs2 = Server.CreateObject ("ADODB. RecordSet ")
Rs2.open sqlstr,conn,0,1
Response.Write ("<table width=" "height=" "0" "border=" "0" "cellpadding=" "1" "Cellspacing=" "# 000000 ">" & vbCrLf)
Response.Write ("<tr>" & vbCrLf)
Response.Write ("<td width=" "117" "height=" "bgcolor=" "#FFFFFF" "><strong> Field name </strong></td > "& vbCrLf)
Response.Write ("<td width=" "height=" "bgcolor=" "#FFFFFF" "><strong> type </strong></td > "& vbCrLf)
Response.Write ("<td width=" "", "height=" "," "bgcolor=" "#FFFFFF" "><strong> length </strong></td > "& vbCrLf)
Response.Write ("<td width=" "height=" "bgcolor=" "#FFFFFF" "><strong> Default value &LT;/STRONG&GT;&LT;/TD > "& vbCrLf)
Response.Write ("<td width=" "", "height=" "," "bgcolor=" "#FFFFFF" "><strong> primary key </strong></td > "& vbCrLf)
Response.Write ("<td width=" "height=" "bgcolor=" "#FFFFFF" "><strong> description </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=" "M" "bgcolor=" "#FFFFFF" ">" & Rs2.fields.item (i). Name & "< /TD> "& vbCrLf)
If issqldatabase = 0 Then
Response.Write ("<td width=" "height=" "bgcolor=" "#FFFFFF" ">" & Accesstypename (i). Type) & "</td>" & vbCrLf)
Else
Response.Write ("<td width=" "height=" "bgcolor=" "#FFFFFF" ">" & Sqltypename (i). Type) & "</td>" & vbCrLf)
End If
Response.Write ("<td width=" "," "height=" "," "bgcolor=" "#FFFFFF" ">" & Rs2.fields.item (i). DefinedSize & "</td>" & vbCrLf)
Response.Write ("<td width=" "height=" "bgcolor=" "#FFFFFF" "> </td>" & vbCrLf)
Response.Write ("<td width=" "", "height=" "," "bgcolor=" "#FFFFFF" "> </td>" & vbCrLf)
Response.Write ("<td width=" "height=" "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> Total </b><font color=red>" & t_count & "</font><b> tables </b></u> "
Response.Write <br/><br/> Description: The representation of the field type with "/" may be one of two, which needs to be determined further; DefinedSize is not accurate when obtaining type field lengths such as text, ntext, and so on. "
Rs.close
Set rs = Nothing
Conn.close
Set conn=nothing

' SQL Server field type conversion
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 field type conversion
Function accesstypename (num)
Select Case num
Case 3
Accesstypename = "Auto number/number"
Case 6
Accesstypename = "Currency"
Case 7
Accesstypename = "Date/Time"
Case 11
Accesstypename = "Yes/No"
Case 202
Accesstypename = "Text"
Case 203
Accesstypename = "Memo/HYPERLINK"
Case 205
Accesstypename = "OLE Object"
End Select
End Function
%>