Previous PageNext Page

9.3.13 OpenSchema Method

Obtains database schema information from the provider.
 

Syntax

Set recordset = connection.OpenSchema (QueryType,

Criteria, SchemaID)

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.

Return Values

Returns a Recordset object that contains schema information.
 

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.
 

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 © 2000 Chili!Soft

Previous PageTop Of PageNext Page