Obtaining Metadata from Linked Servers

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

  • Driver-specific functions from the SQL Server Native Client ODBC driver. For more information, see SQLLinkedServers and SQLLinkedCatalogs.

  • Provider-specific rowsets from the SQL Server Native Client OLE DB Provider. For more information, see OLE DB Schema Rowsets.

  • Several ODBC catalog functions accept two-part names that reference objects on linked servers.

  • System stored procedures similar to the existing catalog stored procedures that return metadata on linked servers.

The following system stored procedures can be used to retrieve metadata 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 metadata extensions. Because this interface is an optional interface, a provider might not implement this interface. Metadata is not available on linked servers that are defined against such providers.