Obtaining Meta Data from Linked Servers

OLE DB providers expose meta data 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™ 2000 uses this interface to get meta data about remote tables. This meta data is exposed to the user through these 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.
Several ODBC catalog functions accept two-part names referencing objects on linked servers.
System stored procedures similar to the existing catalog stored procedures that return meta data on linked servers.

These system stored procedures can be used to retrieve meta data from linked servers.

sp_linkedservers	sp_primarykeys
sp_catalogs	sp_indexes
sp_column_privileges	sp_table_privileges
sp_columns_ex	sp_tables_ex
sp_foreignkeys

Note SQL Server is the only data source against which stored procedures are supported in distributed queries.

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

Obtaining Meta Data from Linked Servers

See Also