Data Management

Much 'ADO' about field properties

Learn how to use ADO's database schema objects to retrieve information about a database at run time.


Q: I’m using ADO to access a SQL Server database, and I need some information about the table I am working with—like the default value of some of its fields. How can I get this information?

A: Microsoft’s ADO data access component provides a method of examining the structure and properties of any OLE-DB compliant data source, otherwise known as its schemas. The OpenSchema method of ADO’s Connection object allows a client application to query an OLE-DB provider for information about itself, one of its catalogs, or one of the objects in that database.

OpenSchema is very powerful and flexible, and it’s capable of communicating a variety of information about the data source. Unfortunately, it’s also a little counterintuitive to use. Let’s take a look at ADO’s schema retrieval abilities. Along the way, we’ll answer your particular question. I’ve also built a sample field property browser program that illustrates the use of OpenSchema to retrieve a list of tables and their columns from a database.

Open Sesame?
OpenSchema returns a plethora of Recordset objects, each with a slightly different structure depending on the particular schema it is currently modeling, which we’ll get into in a little bit. The following code fragment illustrates OpenSchema’s syntax, assuming that the variables rs and cn have been declared:
Set rs=cn.OpenSchema(SchemaId, _
FilterArray(), _
SchemaGUID)

The first parameter, SchemaId, is an enumerated type that identifies the schema we are interested in. SchemaId also controls the layout of the Recordset object that will be returned by the call. To retrieve information about the columns in a table, for example, we would pass adSchemaColumns for this parameter. Table A lists some of the possible values for SchemaId, their meanings, and the structure of the resulting Recordset object.
Table A
SchemaId
Description
Important property fields in Recordset
adSchemaCatalogs
Returns a list of catalogs and information about them
CATALOG_NAME, DESCRIPTION
adSchemaColumns
Returns a list of columns defined for tables in a catalog
TABLE_CATALOG, TABLE_NAME,
COLUMN_NAME, COLUMN_GUID,
COLUMN_HASDEFAULT,
COLUMN_DEFAULT,
IS_NULLABLE, DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
DATETIME_PRECISION,
DOMAIN_NAME, DESCRIPTION

adSchemaIndexes
Returns a list of all indexes defined in a catalog
TABLE_CATALOG, TABLE_NAME,
INDEX_CATALOG, INDEX_NAME,
PRIMARY_KEY, UNIQUE,
CLUSTERED, NULLS, AUTO_UPDATE

adSchemaPrimaryKeys
Returns a list of all primary keys defined in a catalog
TABLE_CATALOG, TABLE_NAME,
COLUMN_NAME, PK_NAME

adSchemaProcedures
Returns a list of the stored procedures defined in a catalog
PROCEDURE_CATALOG,
PROCEDURE_NAME,
PROCEDURE_TYPE,
PROCEDURE_DEFINITION,
DESCRIPTION, DATE_MODIFIED

adSchemaTables
Returns a list of the tables and views present in a catalog
TABLE_CATALOG, TABLE_NAME,
DESCRIPTION, DATE_CREATED,
DATE_MODIFIED

adSchemaProviderSpecific
Used in conjunction with SchemaGUID to specify provider-specific schemas
N/A
Values for SchemaId

Although SQL Server supports all of the schema types I’ve listed, not all OLE-DB providers implement them. The OLE-DB specification requires that only the adSchemaTables, adSchemaColumns, and adSchemaProviderTypes schemas be implemented. So check your provider’s documentation if you have any trouble with a particular schema type.

The second parameter, FilterArray(), is a one-dimensional variant array that allows you to specify which database object’s schema should be retrieved: You could specify the name of a particular table or catalog for example. The arguments are significant by their positions, and the meaning of each position will vary depending on which schema you are requesting through SchemaId. Each element of the array must be either a value or nothing (use VB’s Empty keyword). This will seem weird at first, but think of it like you are feeding parameters to a query, and things will make more sense. Table B summarizes the elements of the FilterArray() argument for each SchemaId.
Table B
SchemaId Number of filter elements Filter elements
adSchemaCatalogs
1
CATALOG_NAME
adSchemaColumns
4

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME

adSchemaIndexes
5

TABLE_CATALOG
TABLE_SCHEMA
INDEX_NAME
TYPE
TABLE_NAME

adSchemaPrimaryKeys
3

PK_TABLE_CATALOG
PK_TABLE_SCHEMA
PK_TABLE_NAME

adSchemaProcedures
4
PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
PROCEDURE_TYPE

adSchemaTables
4

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE

Filter elements for SchemaId

As an example, suppose we wanted to retrieve the list of indexes for the “authors” table in SQL Server’s sample “pubs” catalog. We’d specify “pubs” for the TABLE_CATALOG argument in array element 1 and “authors” for the TABLE_NAME argument in element 5 and leave the others blank, as the following code fragment illustrates:
Set rs=cn.OpenSchema(adSchemaIndexes, _
Array("pubs", _
Empty, _
Empty, _
Empty, _
“authors”))

Notice that the adSchemaIndexes schema expects five elements in the array and that we must include all five of them in the array even though we are specifying an argument only for the first (TABLE_CATALOG) and fifth (TABLE_NAME) elements.

OpenSchema’s final parameter, SchemaGUID, allows you to access any nonstandard, provider-specific schemas your provider implements. SchemaGUID is used only if adProviderSpecific is specified for SchemaId.

The sample field browser application
Now we are ready to specifically answer your question: how to determine the default value for a column in a SQL Server table. As I mentioned, I prepared a sample VB program that illustrates how to retrieve this and other information from the adSchemaColumns schema.

The sample app first attempts to open a connection to the “pubs” catalog on the server specified and then creates a list of tables in that catalog by retrieving the adSchemaTables schema, as shown in the following fragment:
Set rs = m_cn.OpenSchema(adSchemaTables, _
Array("pubs", _
Empty, _
Empty, _
Empty))

When the user selects a table from the list, the program builds a list of columns in that table and displays them in the lboFields ListBox by asking for the adSchemaColumns schema and filtering for the selected table. The code fragment that retrieves the schema looks like this:
Set rs = m_cn.OpenSchema(adSchemaColumns, _
Array("pubs", _
Empty, _
cboTableName.Text, _
Empty))

Finally, when a user selects a field from the field list, the adSchemaColumns schema is retrieved again, this time for the selected field only, and the properties for that field (the fields in the returned Recordset) are displayed in a textbox. Retrieving the final schema looks like this:
Set rs = m_cn.OpenSchema(adSchemaColumns, _
Array("pubs", _
Empty, _
cboTableName.Text, _
lboFields.List(lboFields.ListIndex)))

Two of the fields in the resulting Recordset, COLUMN_HASDEFAULT and COLUMN_DEFAULT, answer your question. If a column has a default value defined, the COLUMN_HASDEFAULT field will be true, and the COLUMN_DEFAULT field will hold the default value or the expression that defines it.

Editor's Picks

Free Newsletters, In your Inbox