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.