The COLUMNS schema rowset returns metadata about all of the columns within the tables that are available to the current data source. Each row in the COLUMNS schema rowset corresponds to one column in a table. In the case of a SAS data set, a column is a variable.
Only 11 of the 28 columns in the OLE DB specification contain data as illustrated by the following table.
| Column Name | Type Indicator | Description | Restrictions Supported? |
|---|---|---|---|
| TABLE_NAME | DBTYPE_WSTR | Table name. This value is either libname.memname (SAS/SHARE and IOM providers) or memname (local provider). | Yes |
| COLUMN_NAME | DBTYPE_WSTR | Column name that corresponds to the SAS variable/column name. | No |
| ORDINAL_POSITION | DBTYPE_UI4 | Column ordinal that corresponds to the SAS variable/column number. Column numbers begin at 1. | No |
| COLUMN_HASDEFAULT | DBTYPE_BOOL | VARIANT_TRUE if the column has a default value. VARIANT_FALSE if there is no default value or if the existence of a default value is unknown. | No |
| COLUMN_FLAGS | DBTYPE_BOOL | A bitmask that describes the column. See Possible COLUMN_FLAGS Values for more information. | No |
| IS_NULLABLE | DBTYPE_UI4 | VARIANT_TRUE if the column can be set to NULL. VARIANT_FALSE if the column cannot be set to NULL (which corresponds to a SAS missing value). | No |
| DATA_TYPE | DBTYPE_UI2 | Column data type. | No |
| CHARACTER_MAXIMUM_LENGTH_TYPE | DBTYPE_UI4 | The maximum possible length in characters of the column value. | No |
| CHARACTER_OCTET_LENGTH | DBTYPE_UI4 | The maximum length in bytes of the column value. | No |
| NUMERIC_PRECISION | DBTYPE_UI2 | The maximum precision of the column. | No |
| DESCRIPTION | DBTYPE_WSTR | The variable/column label. | No |
Restrictions enable an OLE DB consumer to restrict the rows that are returned in a schema rowset by IDBSchemaRowset::GetRowset. A restriction is a value for a specific column. When a restriction is passed to IDBSchemaRowset::GetRowset, all rows that are returned in the schema rowset must match the restriction value on the specified column. Restriction values do not support pattern matching or wildcards. For example, the restriction value D_F matches D_F but not DEF.
An OLE DB consumer can programmatically determine the columns on which the providers support restrictions by calling IDBSchemaRowset::GetSchemas.
Note: For definitions of the type indicators, see the "Type Indicators" topic in the OLE DB specification.
The COLUMN_FLAGS column is a bitmask that describes the column. The DBCOLUMNFLAGS enumerated type lists all the possible bits that could be set in the bitmask.
Note: For more information about DBCOLUMNFLAGS, see the discussion of IColumnsInfo::GetColumnInfo in the OLE DB specification.
All four providers extend the COLUMNS schema rowset to include metadata that is specific to SAS data sets. These custom schema rowset columns include the same information that is returned by the ISASColumnsInfo interface. However, there are some differences in how the information is made available. Specifically, the ISASColumnsInfo interface returns SAS metadata one data set at a time and is limited to OLE DB consumers. By contrast, the COLUMNS schema rowset extensions include all columns (variables) that are in all tables (data sets) in a data source. They are also available to both OLE DB and ADO consumers.
Each custom column maps to a member of the SASCOLUMNINFO structure that is returned by ISASColumnsInfo::GetColumnInfo. A ninth member in that structure, pwszColDesc, does not have a corresponding custom column because it returns a variable label that is included in the OLE DB specification's standard DESCRIPTION column.
The following table lists the columns that are added to the COLUMNS schema rowset. The columns are returned following the standard columns in the OLE DB specification and in the order in which they appear in the following table.
| Column Name | Type Indicator | Mapped Member | Description |
|---|---|---|---|
| FORMAT_NAME | DBTYPE_WSTR | pwszFmtName | Stored format name. If no format is associated with this column, this member is NULL. |
| FORMAT_LENGTH | DBTYPE_I2 | iFmtLength | Stored width of the formatted data. |
| FORMAT_DECIMAL | DBTYPE_I2 | iFmtDecimal | Stored decimal width of the formatted data. |
| INFORMAT_NAME | DBTYPE_WSTR | pwszIFmtName | Stored informat name. |
| INFORMAT_LENGTH | DBTYPE_I2 | iIFmtLength | Stored width to use when applying the default informat. |
| INFORMAT_DECIMAL | DBTYPE_I2 | iIFmtDecimal | Stored decimal width to use when applying the default informat. |
| SORT_ORDER | DBTYPE_I2 | iSortInfo | A signed short value that indicates the column's position in any applied sorting hierarchy. Not valid for all providers and only applicable if the data set is the result of SORT procedure. See What SORT_ORDER Values Mean for more information. |
| DBTYPE_INDEXED | DBTYPE_BOOL | fIndexed | VARIANT_TRUE when the variable is indexed; otherwise, it is VARIANT_FALSE. |
Note: For definitions of the type indicators, see "Type Indicators" in Microsoft's OLE DB specification documentation.
If a data set has been produced by a SORT procedure (PROC SORT), then the SORT_ORDER custom column contains a signed short value that indicates the column's position in the applied sorting hierarchy specified in the BY statement. Positive values indicate ascending sort order, and negative values indicate descending sort order. The absolute value of the signed short value describes the position of the variable in the sorting hierarchy. Zero (0) indicates that the column does not participate in sorting.
The meaning of the value in the SORT_ORDER custom column depends on the data provider and the consumer: