Sun
ONE Active Server Pages Product Home Page Developer Site Version
 

ContentsPreviousNextIndex



ADO Connection Object OpenSchema Method

Obtains database schema information from the provider.

OpenSchema Method Syntax
Set recordset = connection.OpenSchema (QueryType, 
 Criteria, SchemaID)
OpenSchema Method Parameters

QueryType

The type of schema query to run. Can be any of the constants listed below.

Criteria

Optional array of query constraints for each QueryType option, as listed below:

QueryType values
Criteria values
adSchemaAsserts
CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
Not currently supported on UNIX.
adSchemaCatalogs
CATALOG_NAME
Not currently supported on UNIX.
asSchemaCharacterSets
CHARACTER_SET_CATALOG
CHARACTER_SET_SCHEMA
CHARACTER_SET_NAME
Not currently supported on UNIX.
adSchemaCheckConstraints
CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
Not currently supported on UNIX.
adSchemaCollations
COLLATION_CATALOG
COLLATION_SCHEMA
COLLATION_NAME
Not currently supported on UNIX.
adSchemaColumnDomainUsage
DOMAIN_CATALOG
DOMAIN_SCHEMA
DOMAIN_NAME
COLUMN_NAME
Not currently supported on UNIX.
adSchemaColumnPrivileges
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
GRANTOR
GRANTEE
Not currently supported on UNIX.
adSchemaColumns
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
Supported on UNIX.
adSchemaConstraintTableUsage
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
Not currently supported on UNIX.
adSchemaForeignKeys
PK_TABLE_CATALOG
PK_TABLE_SCHEMA
PK_TABLE_NAME
FK_TABLE_CATALOG
FK_TABLE_SCHEMA
FK_TABLE_NAME
Not currently supported on UNIX.
adSchemaIndexes
TABLE_CATALOG
TABLE_SCHEMA
INDEX_NAME
TYPE
TABLE_NAME
Not currently supported on UNIX.
adSchemaKeyColumnUsage
CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
Not currently supported on UNIX.
adSchemaPrimaryKeys
PK_TABLE_CATALOG
PK_TABLE_SCHEMA
PK_TABLE_NAME
Not currently supported on UNIX.
adSchemaProcedureColumns
PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
COLUMN_NAME
Not currently supported on UNIX.
adSchemaProcedures
PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
PARAMETER_TYPE
Not currently supported on UNIX.
adSchemaProviderSpecific
see Remarks
Not currently supported on UNIX.
adSchemaProviderTypes
DATA_TYPE
BEST_MATCH
Supported on UNIX.
adSchemaReferentialConstraints
CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
Not currently supported on UNIX.
adSchemaSchemata
CATALOG_NAME
SCHEMA_NAME
SCHEMA_OWNER
Not currently supported on UNIX.
adSchemaSQLLanguages
<none>
Not currently supported on UNIX.
adSchemaStatistics
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
Not currently supported on UNIX.
adSchemaTableConstraints
CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
CONSTRAINT_TYPE
Not currently supported on UNIX.
adSchemaTablePrivileges
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
Not currently supported on UNIX.
adSchemaTables
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
Supported on UNIX.
adSchemaTranslations
TRANSLATION_CATALOG
TRANSLATION_SCHEMA
TRANSLATION_NAME
Not currently supported on UNIX.
adSchemaUsagePrivileges
OBJECT_CATALOG
OBJECT_SCHEMA
OBJECT_NAME
OBJECT_TYPE
GRANTOR
GRANTEE
Not currently supported on UNIX.
adSchemaViewColumnUsage
VIEW_CATALOG
VIEW_SCHEMA
VIEW_NAME
Not currently supported on UNIX.
adSchemaViewTableUsage
VIEW_CATALOG
VIEW_SCHEMA
VIEW_NAME
Not currently supported on UNIX.
adSchemaViews
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
Not currently supported on UNIX.

SchemaID

The GUID for a provider-schema schema query is not defined by the OLE DB 1.1 specification. This parameter is required if QueryType is set to adSchemaProviderSpecific; otherwise, it is not used.

OpenSchema Method Return Values

Returns an ADO Recordset Object that contains schema information.

OpenSchema Method Remarks

The OpenSchema method returns information about the data source, such as information about the tables on the server and the columns in the tables.

The Criteria argument is an array of values that can be used to limit the results of a schema query. Each schema query has a different set of parameters that it supports. The actual schemas are defined by the OLE DB specification under the "IDBSchemaRowset" interface. The ones supported in ADO 1.5 are listed above.

The constant adSchemaProviderSpecific is used for the QueryType argument if the provider defines its own non-standard schema queries outside those listed above. When this constant is used, the SchemaID argument is required to pass the GUID of the schema query to execute. If QueryType is set to adSchemaProviderSpecific but SchemaID is not provided, an error will result.

Providers are not required to support all of the OLE DB standard schema queries. Specifically, only adSchemaTables, adSchemaColumns and adSchemaProviderTypes are required by the OLE DB specification. However, the provider is not required to support the Criteria constraints listed above for those schema queries.

OpenSchema Method Example

This Visual Basic example uses the OpenSchema method to display the name and type of each table in the Pubs database.

Public Sub OpenSchemaX() 
Dim cnn1 As ADODB.Connection 
Dim rstSchema As ADODB.Recordset 
Dim strCnn As String 
Set cnn1 = New ADODB.Connection 
strCnn = "driver={SQL Server};server=srv;" & _ 
"uid=sa;pwd=;database=pubs" 
cnn1.Open strCnn 
Set rstSchema = cnn1.OpenSchema(adSchemaTables) 
Do Until rstSchema.EOF 
Debug.Print "Table name: " & _ 
rstSchema!TABLE_NAME & vbCr & _ 
"Table type: " & rstSchema!TABLE_TYPE & vbCr 
rstSchema.MoveNext 
Loop
rstSchema.Close 
cnn1.Close 
End Sub


ContentsPreviousNextIndex