Table Name Resolution and Metadata Retrieval

SQL Server resolves a given remote table name in a distributed query to a specific table or view in an OLE DB data source. Both the linked server–based and ad hoc naming schemes result in a three-part name to be interpreted by the provider. In the case of the linked server–based name, the last three parts of the four-part name form the catalog, schema, and object names. In the case of the ad hoc name, the third argument of the OPENROWSET function specifies a three-part name that describes the catalog, schema, and object names. One or both of the catalog and schema names can be empty. (A four-part name with an empty catalog name and schema name looks like <server-name>…<object-name>.) In such a case, SQL Server uses NULL as the corresponding value to look for in the schema rowset tables.

The name resolution rules and the metadata retrieval steps that SQL Server employs depend on whether the provider supports the IDBSchemaRowset interface on the Session object.

If IDBSchemaRowset is supported, TABLES, COLUMNS, INDEXES, and TABLES_INFO schema rowsets are used from the IDBSchemaRowset interface. (The TABLES_INFO schema rowset is defined in OLE DB 2.0.) SQL Server restricts the schema rowsets returned by the IDBSchemaRowset interface to look for schema rows that match the specified remote table name parts. The following are the rules related to the restrictions supported by the provider on the schema rowsets and how SQL Server uses them to retrieve a remote table’s metadata:

• Restrictions on TABLE_NAME and COLUMN_NAME columns are always required.
• If the provider supports a restriction on TABLE_CATALOG (or TABLE_SCHEMA), SQL Server uses that restriction on TABLE_CATALOG (or TABLE_SCHEMA). If catalog (or schema) name is not specified in the remote table name, a null value is used as the corresponding restriction value. If a catalog (or schema) name is specified, the provider must support the corresponding restriction on TABLE_CATALOG (or TABLE_SCHEMA).
• The provider must either support restriction on the TABLE_SCHEMA column in both TABLES and COLUMNS or support them on neither. The provider must either support catalog name restriction on both TABLES and COLUMNS rowsets or support them on neither.
• If any restrictions are supported on INDEXES, the provider must support schema restriction on both TABLES and INDEXES or support them on neither. The provider must either support catalog name restriction on both TABLES and INDEXES rowsets or support them on neither.

From the TABLES schema rowset, SQL Server retrieves the TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_GUID columns by setting restrictions according to the above rules.

From the COLUMNS schema rowset, SQL Server retrieves the TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_GUID, ORDINAL_POSITION, COLUMN_FLAGS, IS_NULLABLE, DATA_TYPE, TYPE_GUID, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, and NUMERIC_SCALE columns. COLUMN_NAME, DATA_TYPE and ORDINAL_POSITION must return valid nonnull values. If DATA_TYPE is DBTYPE_NUMERIC or DBTYPE_DECIMAL, the corresponding NUMERIC_PRECISION and NUMERIC_SCALE must be valid nonnull values.

From the optional INDEXES schema rowset, SQL Server looks for indexes on the specified remote table by setting restrictions as per the previous rules. From the matching index entries thus found, SQL Server retrieves the TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, INDEX_CATALOG, INDEX_SCHEMA, INDEX_NAME, PRIMARY_KEY, UNIQUE, CLUSTERED, FILL_FACTOR, ORDINAL_POSITION, COLUMN_NAME, COLLATION, CARDINALITY, and PAGES columns.

From the optional TABLES_INFO rowset, SQL Server looks for additional information on the specified remote table such as bookmark support, the type and the length of the bookmark. All columns except the DESCRIPTION column of the TABLES_INFO rowset are used. The information in TABLES_INFO rowset is used as follows:

• The BOOKMARK_DURABILITY column is used to implement more efficient keyset cursors. If this column has a value of BMK_DURABILITY_INTRANSACTION or a higher durability value, SQL Server uses bookmark-based retrieval and updates of remote table rows for implementing a keyset cursor.
• The BOOKMARK_TYPE, BOOKMARK_DATA TYPE, and BOOKMARK_MAXIMUM_LENGTH columns are used to determine bookmark metadata at query compilation time. If these columns are not supported, SQL Server opens the base table rowset through IOpenRowset during compilation to get the bookmark information.

If IDBSchemaRowset is not supported and the remote table name includes a catalog or schema name, SQL Server requires IDBSchemaRowset and returns an error. However, if neither the catalog nor the schema names are supplied, SQL Server opens the rowset that corresponds to the remote table and retrieves the column metadata from the mandatory IColumnsInfo interface of the rowset object.

SQL Server opens the rowset corresponding to the table by calling IOpenRowset::OpenRowset. The table name supplied to OPENROWSET is constructed from the catalog, schema, and object name parts.

• Each of the name parts (catalog, schema, object name) are quoted with the provider’s quoting character (DBLITERAL_QUOTE) and then concatenated with the DBLITERAL_CATALOG_SEPARATOR character and the DBLITERAL_SCHEMA_SEPARATOR character embedded between them. The name construction follows the OLE DB rules in IOpenRowset.
• The column metadata for the table is retrieved through IColumnsInfo::GetColumnInfo after the rowset object is opened.

If IDBSchemaRowset is not supported with TABLES, COLUMNS, and TABLES_INFO rowsets, SQL Server opens the rowset against the base table twice: once during query compilation to retrieve metadata, and once during query execution. Providers that incur side effects from opening the rowset (for example, run code that alters the state of a real-time device, send e-mail, run arbitrary user-supplied code) must be aware of this behavior.