|
ADO Schemas to list tables
& fields
You can examine a Schema for the table names
and column names and column detail information.
filename=/learn/test/dbschemas.asp
<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:
filename=/learn/test/lib_fieldtypes.asp
<%
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
%>
|