Asp Codes
Asp > ADO sample source codes

ADO Schemas to list tables & fields

ADO Schemas to list tables & fields

<html><head>
<TITLE>dbschemas.asp</TITLE>
</head>
<body bgcolor="#FFFFFF">
<!--#INCLUDE VIRTUAL="/ADOVBS.INC" -->
<!--#INCLUDE VIRTUAL="/learn/test/lib_fieldtypes.asp" -->
<% 
myDSN="DSN=Student;uid=student;pwd=magic"

Set conntemp=Server.CreateObject("adodb.connection")
conntemp.open myDSN

Set rsSchema = conntemp.OpenSchema(adSchemaColumns)
thistable=""
pad="   "
Do UNTIL rsSchema.EOF
prevtable=thistable
thistable=rsSchema("Table_Name")
thiscolumn=rsSchema("COLUMN_NAME")
If thistable<>prevtable Then
Response.Write "Table=<b>" & thistable & "</b><br>"
Response.Write "TABLE_CATALOG=<b>" & rsSchema("TABLE_CATALOG") & "</b><br>"
Response.Write "TABLE_SCHEMA=<b>" & rsSchema("TABLE_SCHEMA") & "</b><p>"
End If
Response.Write "<br>" & pad & "Field=<b>" & thiscolumn & "</b><br>"
Response.Write pad & "Type=<b>" & fieldtypename(rsSchema("DATA_TYPE")) & "</b><br>"


Dim colschema(27)
colschema(0)="TABLE_CATALOG"
colschema(1)="TABLE_SCHEMA"
colschema(2)="TABLE_NAME"
colschema(3)="COLUMN_NAME"
colschema(4)="COLUMN_GUID"
colschema(5)="COLUMN_PROP_ID"
colschema(6)="ORDINAL_POSITION"
colschema(7)="COLUMN_HASDEFAULT"
colschema(8)="COLUMN_DEFAULT"
colschema(9)="COLUMN_FLAGS"
colschema(10)="IS_NULLABLE"
colschema(11)="DATA_TYPE"
colschema(12)="TYPE_GUID"
colschema(13)="CHARACTER_MAXIMUM_LENGTH"
colschema(14)="CHARACTER_OCTET_LENGTH"
colschema(15)="NUMERIC_PRECISION"
colschema(16)="NUMERIC_SCALE"
colschema(17)="DATETIME_PRECISION"
colschema(18)="CHARACTER_SET_CATALOG"
colschema(19)="CHARACTER_SET_SCHEMA"
colschema(20)="CHARACTER_SET_NAME"
colschema(21)="COLLATION_CATALOG"
colschema(22)="COLLATION_SCHEMA"
colschema(23)="COLLATION_NAME"
colschema(24)="DOMAIN_NAME"
colschema(25)="DOMAIN_CATALOG"
colschema(26)="DOMAIN_SCHEMA"
colschema(27)="DESCRIPTION"

On Error Resume Next
For counter=4 To 27
thisColInfoType=colschema(counter)
thisColInfo=rsSchema(thisColInfoType)
If Err.number<>0 Then
thiscolinfo="-error-"
Err.Clear
End If
If thisColInfo<>"" Then
Response.Write pad & pad & pad & thiscolinfotype 
Response.Write "=<b>" & thiscolinfo & "</b><br>"
End If
Next
Response.Flush
rsSchema.MoveNext
Loop

rsSchema.Close
Set rsSchema=Nothing

conntemp.close
Set conntemp=Nothing
%>
</body></html>


Here Is the contents of lib_fieldtypes.asp which Is included To make this example work:

<%
Function fieldtypename(parm1)
Select Case Parm1
Case 0
fieldtypename="adEmpty"
Case 16
fieldtypename="adTinyInt"
Case 2
fieldtypename="adSmallInt"
Case 3
fieldtypename="adInteger"
Case 20
fieldtypename="adBigInt"
Case 17
fieldtypename="adUnsignedTinyInt"
Case 18
fieldtypename="adUnsignedSmallInt"
Case 19
fieldtypename="adUnsignedInt"
Case 21
fieldtypename="adUnsignedBigInt"
Case 4
fieldtypename="adSingle"
Case 5
fieldtypename="adDouble"
Case 6
fieldtypename="adCurrency"
Case 14
fieldtypename="adDecimal"
Case 131
fieldtypename="adNumeric"
Case 11
fieldtypename="adBoolean"
Case 10
fieldtypename="adError"
Case 132
fieldtypename="adUserDefined"
Case 12
fieldtypename="adVariant"
Case 9
fieldtypename="adIDispatch"
Case 13
fieldtypename="adIUnknown"
Case 72
fieldtypename="adGUID"
Case 7
fieldtypename="adDate"
Case 133
fieldtypename="adDBDate"
Case 134
fieldtypename="adDBTime"
Case 135
fieldtypename="adDBTimeStamp"
Case 8
fieldtypename="adBSTR"
Case 129
fieldtypename="adChar"
Case 200
fieldtypename="adVarChar"
Case 201
fieldtypename="adLongVarChar"
Case 130
fieldtypename="adWChar"
Case 202
fieldtypename="adVarWChar"
Case 203
fieldtypename="adLongVarWChar"
Case 128
fieldtypename="adBinary"
Case 204
fieldtypename="adVarBinary"
Case 205
fieldtypename="adLongVarBinary"
Case Else
fieldtypename="Undefined by ADO"
End Select
End Function
%>