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
Copyright © 2003 Sun Microsystems, Inc. All rights reserved.