ADO » Connection » OpenSchema

Syntax:
Set rsobject = connectionobject.OpenSchema (QueryType, Criteria, SchemaID)
QueryType
The mandatory QueryType parameter is one of the SchemaEnum constants that describes the type of schema to use. There are 41 types of these SchemaEnum values to choose from. However, OLE DB specifications only require that three of these possibilities be supported. They are, adSchemaColumns, adSchemaProviderTypes, and adSchemaTables. Further, the provider is not required by OLE DB to support the Constraint Columns values imposed by the optional Criteria parameter (discussed below) for these three schemas.
Criteria
The optional Criteria parameter is a variant composed of an array of query constraints for each of the values in the SchemaEnum constants. These are called Constraint Columns and they set limits to the results obtained using a schema query.
SchemaID
The SchemaID parameter is required if the QueryType parameter is set to the adSchemaProviderSpecific constant. This indicates that the query is a provider schema that is not defined by OLE DB specifications.

Returns descriptive schema information from the provider about the data source.

The OpenSchema method returns a read-only RecordSet object that contains schema (which means self-descriptive) information about the data source. For example, schema information can include the names of the tables, the names of the columns in the tables, and the data type of each column.
 
There is one mandatory parameter and two optional parameters.
  
SchemaEnum Constants
 

Constant Value Description
adSchemaColumns 4 Request column information
adSchemaProviderTypes 22 Request provider type information
adSchemaTables 20 Request table information

   
SchemaEnum Constants and Constraint Columns Values
 
Constant Value Description Constraint Columns
adSchemaAsserts 0 Returns the assertions CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
adSchemaCatalogs 1 Returns the catalog information CATALOG_NAME
adSchemaCharacterSets 2 Returns the defined character set in the catalog CHARACTER_SET_CATALOG
CHARACTER_SET_SCHEMA
CHARACTER_SET_NAME
adSchemaCheckConstraints 5 Returns the defined check constraints in the catalog CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
adSchemaCollations 3 Returns the defined character collations in the catalog COLLATION_CATALOG
COLLATION_SCHEMA
COLLATION_NAME
adSchemaColumnDomainUsage 11 Returns the columns that are domain dependent DOMAIN_CATALOG
DOMAIN_SCHEMA
DOMAIN_NAME
COLUMN_NAME
adSchemaColumnPrivileges 13 Returns the column privilege information TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
GRANTOR
GRANTEE
adSchemaColumns 4 Returns the columns information TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
adSchemaConstraintColumnUsage 6 Returns the columns used by constraints COLUMN_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
adSchemaConstraintTableUsage 7 Returns the tables used by constraints TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
adSchemaCubes 32 Returns info about the cubes used for multi-dimensional data CATALOG_NAME
SCHEMA_NAME
CUBE_NAME
adSchemaDBInfoKeywords 30 Return the keywords recognized by the provider none
adSchemaDBInfoLiterals 31 Return the literals used in text commands by the provider none
adSchemaDimensions 33 Returns info about the dimensions in a cube CATALOG_NAME
SCHEMA_NAME
CUBE_NAME
DIMENSION_NAME
DIMENSION_UNIQUE_NAME
adSchemaForeignKeys 27 Returns the foreign key column PK_TABLE_CATALOG
PK_TABLE_SCHEMA
PK_TABLE_NAME
FK_TABLE_CATALOG
FK_TABLE_SCHEMA
FK_TABLE_NAME
adSchemaHierarchies 34 Returns hierarchy info for multi-dimensional data CATALOG_NAME
SCHEMA_NAME
CUBE_NAME
DIMENSION_UNIQUE_NAME
HIERARCHY_NAME
HIERARCHY_UNIQUE_NAME
adSchemaIndexes 12 Returns the indexes defined in a catalog TABLE_CATALOG
TABLE_SCHEMA
INDEX_NAME
TYPE
TABLE_NAME
adSchemaKeyColumnUsage 8 Returns the defined key columns in the catalog COLUMN_NAME
CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
adSchemaLevels 35 Returns info about the levels in multi-dimensional data CATALOG_NAME
SCHEMA_NAME
CUBE_NAME
DIMENSION_UNIQUE_NAME
HIERARCHY_UNIQUE_NAME
LEVEL_NAME
LEVEL_UNIQUE_NAME
adSchemaMeasures 36 Returns the measures for multi-dimensional data CATALOG_NAME
SCHEMA_NAME
CUBE_NAME
MEASURE_NAME
MEASURE_UNIQUE_NAME
adSchemaMembers 38 Returns the available members for multi-dimensional data CATALOG_NAME
SCHEMA_NAME
CUBE_NAME
DIMENSION_UNIQUE_NAME
HIERARCHY_UNIQUE_NAME
LEVEL_UNIQUE_NAME
LEVEL_NUMBER
MEMBER_NAME
MEMBER_UNIQUE_NAME
MEMBER_CAPTION
MEMBER_TYPE
TREE OPERATOR
adSchemaPrimaryKeys 28 Returns the primary key columns PK_TABLE_CATALOG
PK_TABLE_SCHEMA
PK_TABLE_NAME
adSchemaProcedureColumns 29 Returns info on columns returned by stored procedures PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
COLUMN_NAME
adSchemaProcedureParameters 26 Returns info on the parameters and return codes of stored procedures PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
PARAMETER_NAME
adSchemaProcedures 16 Returns info on the stored procedures PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
PROCEDURE_TYPE
adSchemaProperties 37 Returns info on the properties of each level of multi-dimensional data CATALOG_NAME
SCHEMA_NAME
CUBE_NAME
DIMENSION_UNIQUE_NAME
HIERARCHY_UNIQUE_NAME
LEVEL_UNIQUE_NAME
MEMBER_UNIQUE_NAME
PROPERTY_TYPE
PROPERTY_NAME
adSchemaProviderSpecific -1 For provider-specific, non-standard schema provider specfic
adSchemaProviderTypes 22 Returns the provider-supported data types DATA_TYPE
BEST_MATCH
adSchemaReferentialConstraints 9 Returns the defined referential constraints in the catalog CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
adSchemaSchemata 17 Returns the schema owned by a user CATALOG_NAME
SCHEMA_NAME
SCHEMA_OWNER
adSchemaSQLLanguages 18 Returns the SQL language support info none
adSchemaStatistics 19 Returns the defined statistics in a catalog TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
adSchemaTableConstraints 10 Returns the table contraints defined in the catalog CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
CONSTRAINT_TYPE
adSchemaTablePrivileges 14 Returns the privileges on tables TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
GRANTOR
GRANTEE
adSchemaTables 20 Returns the tables in a catalog TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
adSchemaTranslations 21 Returns the character set translation info TRANSLATION_CATALOG
TRANSLATION_SCHEMA
TRANSLATION_NAME
adSchemaTrustees 39 Not used none
adSchemaUsagePrivileges 15 Returns the user privilege info OBJECT_CATALOG
OBJECT_SCHEMA
OBJECT_NAME
OBJECT_TYPE
GRANTOR
GRANTEE
adSchemaViewColumnUsage 24 Returns the column usage for viewed tables VIEW_CATALOG
VIEW_SCHEMA
VIEW_NAME
adSchemaViews 23 Returns the views info from the catalog TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
adSchemaViewTableUsage 25 Returns the table usage for viewed tables VIEW_CATALOG
VIEW_SCHEMA
VIEW_NAME

Examples

Code:
Set objRecordset = objConnection.OpenSchema(adSchemaTables)

See Also: