Metadata Discovery

This section describes how to discover metadata using the linked server, through either information schema queries or SQL Server Management Studio navigation.

Information Schema Queries

SQL Server provides an information schema that you can query to view SQL Server metadata (such as lists of tables, columns, etc.) on your linked server.

To retrieve a list of tables in use on your linked server or metadata information about the columns/fields on your linked server, use native SQL Server procedures built for this purpose. These methods are designed to return the metadata mapped to the appropriate SQL Server type.

For example:

EXEC sp_tables_ex @table_server = 'linkedservername', @table_catalog='CData Sage50UK Sys', @table_schema='sage50uk'

or

EXEC sp_columns_ex @table_server = 'linkedservername', @table_catalog = 'CData Sage50UK Sys', @table_schema = 'sage50uk', @table_name = 'TradingAccounts'

SSMS Navigation

You can also view metadata information on the linked server via SQL Server Management Studio's Object Explorer.

To view the tables that are available through the linked server, navigate to the Object Explorer and open Server Objects > Linked Servers > YourLinkedServerName > Catalogs > YourCatalogName > Tables.