COLUMNS Schema Rowset

Standard Mapping

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.

The following table lists the 11 columns (of the 28 columns in the OLE DB specification) that contain data.

Standard Mapping

Column Name	Type Indicator	Mapped Value	Restrictions Supported?
TABLE_NAME	DBTYPE_WSTR	Table name. This value is either libname.memname (SAS/SHARE, IOM, and Base SAS providers) or the data set name (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.)	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 SAS variable (column) label.	No

Note: For more information about restrictions, see How to Restrict the Rows That Are Returned.

Rowset Extensions

All the SAS 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.

Rowset Extensions

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. This member is not valid for all providers, and it is applicable only if the data set is the result of a SORT procedure. (See What SORT_ORDER Means.)
DBTYPE_INDEXED	DBTYPE_BOOL	fIndexed	VARIANT_TRUE when the variable is indexed; otherwise, it is VARIANT_FALSE.

Additional Details

Possible COLUMN_FLAGS Values

The COLUMN_FLAGS column is a bitmask that describes the column. The DBCOLUMNFLAGS enumerated type lists all of the possible bits that could be set in the bitmask.

DBCOLUMNFLAGS_ISFIXEDLENGTH	Set only if all data in the column has the same length. This flag is set for columns that contain numeric data. If DBPROP_SAS_BLANKPADDING is True, then this flag is set for columns containing character data. Otherwise, this flag is not set.
DBCOLUMNFLAGS_MAYBENULL	Set if the column can contain NULL values (which correspond to missing values). This flag is used by OLE DB consumers that read data to determine whether they might encounter a missing value.
DBCOLUMNFLAGS_WRITE	Set only if IRowsetChange::SetData can be called for the column.

Note: For more information about DBCOLUMNFLAGS, see the discussion of IColumnsInfo::GetColumnInfo in the OLE DB specification.

What SORT_ORDER Means

If a data set has been produced by a SORT procedure (PROC SORT), then the SORT_ORDER custom column contains a signed short value. This value 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:

The local provider supports this column for both OLE DB and ADO consumers.
The SAS/SHARE and Base SAS providers support this column only for OLE DB consumers. When used with an ADO consumer, this column contains zero when it participates in sorting.
The IOM provider does not support this column at all, so the column always contains zero when it participates in sorting.