Search Search

#1 worldwide
FREE Coding Lessons
since 1996
   THE BEST WAY to learn ASP & Asp.net!
Advertise Here!
click for details 		Credits Host:
DiscountASP.net 		Server Admin:
The "Team" 		Contact Info.
Charles M. Carroll
my Blog
[prev. Lesson]  ADO: Tables within Databases
     [next Lesson]  ADO: Schemas to access All Data

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

<Test Script Below>


<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="&nbsp;&nbsp;&nbsp;"
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

<Test Script Below>


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

Send Us a Holiday Gift!. Charles celebrates Christmas, Jewish holidays, Kwanza, Chinese New Year, Japanese Holidays, Secretary Day, High Muslim Holy Days, Pagan & Wicca holidays, and many more! - send a gift any HOLIDAY. 2004 gift gallery & 2005 gift gallery