Obtaining Metadata from Linked Servers

OLE DB providers expose metadata about their data through the IDBSchemaRowset interface, which can be used to retrieve information in the form of OLE DB rowset objects. Microsoft® SQL Server™ uses this interface to get metadata about remote tables. This metadata is exposed to the user through three interfaces:

Driver-specific functions from the SQL Server ODBC driver. For more information, see SQLLinkedServers and SQLLinkedCatalogs.
Provider-specific rowsets from the SQL Server OLE DB provider. For more information, see Schema Rowsets.
Serveral ODBC catalog functions accept two-part names referencing objects on linked servers.
System stored procedures, similar to the existing catalog stored procedures, that return metadata on linked servers.

The system stored procedures that can be used to retrieve metadata from linked servers include:

sp_linkedservers	sp_primarykeys
sp_catalogs	sp_indexes
sp_column_privileges	sp_table_privileges
sp_columns_ex	sp_tables_ex
sp_foreignkeys

SQL Server uses the IDBSchemaRowset interface of the OLE DB provider to implement these metadata extensions. Because this interface is an optional interface, a provider may not implement this interface. On linked servers defined against such providers, no metadata is available.

Obtaining Metadata from Linked Servers

See Also