|
ADO Schemas/listing tables and fields
You can examine a Schema for the table names
and column names and column detail information.
filename=/learn/test/dbschemasall.asp
<html><head>
<TITLE>dbschemasall.asp</TITLE>
</head>
<body bgcolor="#FFFFFF">
<!--#INCLUDE VIRTUAL="/ADOVBS.INC" -->
<!--#INCLUDE VIRTUAL="/learn/test/lib_schemas.asp" -->
<%
myDSN="DSN=Student;uid=student;pwd=magic"
DIM colschema(30)
colschema(0)=adSchemaProviderSpecific
colschema(1)=adSchemaAsserts
colschema(2)=adSchemaCatalogs
colschema(3)=adSchemaCharacterSets
colschema(4)=adSchemaCollations
colschema(5)=adSchemaColumns
colschema(6)=adSchemaCheckConstraints
colschema(7)=adSchemaConstraintColumnUsage
colschema(8)=adSchemaConstraintTableUsage
colschema(9)=adSchemaKeyColumnUsage
colschema(10)=adSchemaReferentialContraints
colschema(11)=adSchemaTableConstraints
colschema(12)=adSchemaColumnsDomainUsage
colschema(13)= adSchemaIndexes
colschema(14)=adSchemaColumnPrivileges
colschema(15)=adSchemaTablePrivileges
colschema(16)=adSchemaUsagePrivileges
colschema(17)=adSchemaProcedures
colschema(18)=adSchemaSchemata
colschema(19)=adSchemaSQLLanguages
colschema(20)=adSchemaStatistics
colschema(21)=adSchemaTables
colschema(22)=adSchemaTranslations
colschema(23)=adSchemaProviderTypes
colschema(24)=adSchemaViews
colschema(25)=adSchemaViewColumnUsage
colschema(26)=adSchemaViewTableUsage
colschema(27)=adSchemaProcedureParameters
colschema(28)=adSchemaForeignKeys
colschema(29)=adSchemaPrimaryKeys
colschema(30)=adSchemaProcedureColumns
FOR counter=1 to 30
If counter<>2 then
thisSchema=colSchema(counter)
Call Schema2Table(myDSN,thisSchema)
response.write "<p>"
End If
NEXT
%>
</body></html>
The include file lib_schemas.asp looks like this:
filename=/learn/test/lib_schemas.asp
<%
FUNCTION schemaName(parm1)
SELECT CASE parm1
CASE adSchemaProviderSpecific
schemaname="adSchemaProviderSpecific"
CASE adSchemaAsserts
schemaName="adSchemaAsserts"
CASE adSchemaCatalogs
schemaName="adSchemaCatalogs"
CASE adSchemaCharacterSets
schemaName="adSchemaCharacterSets"
CASE adSchemaCollations
schemaName="adSchemaCollations"
CASE adSchemaColumns
schemaName="adSchemaColumns"
CASE adSchemaCheckConstraints
schemaName="adSchemaCheckConstraints"
CASE adSchemaConstraintColumnUsage
schemaName="adSchemaConstraintColumnUsage"
CASE adSchemaConstraintTableUsage
schemaName="adSchemaConstraintTableUsage"
CASE adSchemaKeyColumnUsage
schemaName="adSchemaKeyColumnUsage"
CASE adSchemaReferentialContraints
schemaName="adSchemaReferentialContraints"
CASE adSchemaTableConstraints
schemaName="adSchemaTableConstraints"
CASE adSchemaColumnsDomainUsage
schemaName="adSchemaColumnsDomainUsage"
CASE adSchemaIndexes
schemaName="adSchemaIndexes"
CASE adSchemaColumnPrivileges
schemaName="adSchemaColumnPrivileges"
CASE adSchemaTablePrivileges
schemaName="adSchemaTablePrivileges"
CASE adSchemaUsagePrivileges
schemaName="adSchemaUsagePrivileges"
CASE adSchemaProcedures
schemaName="adSchemaProcedures"
CASE adSchemaSchemata
schemaName="adSchemaSchemata"
CASE adSchemaSQLLanguages
schemaName="adSchemaSQLLanguages"
CASE adSchemaStatistics
schemaName="adSchemaStatistics"
CASE adSchemaTables
schemaName="adSchemaTables"
CASE adSchemaTranslations
schemaName="adSchemaTranslations"
CASE adSchemaProviderTypes
schemaName="adSchemaProviderTypes"
CASE adSchemaViews
schemaName="adSchemaViews"
CASE adSchemaViewColumnUsage
schemaName="adSchemaViewColumnUsage"
CASE adSchemaViewTableUsage
schemaName="adSchemaViewTableUsage"
CASE adSchemaProcedureParameters
schemaName="adSchemaProcedureParameters"
CASE adSchemaForeignKeys
schemaName="adSchemaForeignKeys"
CASE adSchemaPrimaryKeys
schemaName="adSchemaPrimaryKeys"
CASE adSchemaProcedureColumns
schemaName="adSchemaProcedureColumns"
CASE ELSE
schemaName="-unknown-"
END SELECT
END FUNCTION
SUB Schema2Table(parmDSN, parmSchemaName)
set conntemp=server.createobject("adodb.connection")
conntemp.open parmDSN
on error resume next
set rsSchema=conntemp.OpenSchema(parmSchemaName)
IF err.number=3251 THEN
response.flush
response.write "<b>" & SchemaName(parmSchemaName)
response.write "</b><br> is not supported<br>"
err.clear
ELSE
Call Schema2Table(thisSchema)
response.write "<P><b>" & schemaName(parmSchemaName) & "</b><br>"
response.write "<table border=1><tr>"
'Put Headings On The Table of Field Names
for each whatever in rsSchema.fields
response.write "<td><b>" & whatever.name & "</b></td>"
next
response.write "</tr>"
DO UNTIL rsSchema.eof
response.write "<tr>"
for each whatever in rsSchema.fields
thisfield=whatever.value
if isnull(thisfield) then
thisfield=" "
end if
if trim(thisfield)="" then
thisfield=" "
end if
response.write "<td valign=top>" & thisfield & "</td>"
next
response.write "</tr>"
rsSchema.MoveNext
LOOP
response.write "</table><br>"
response.flush
END IF
rsSchema.Close
set rsSchema=nothing
conntemp.close
set conntemp=nothing
END SUB
%>
|