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

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