Obtaining General Information About Linked Servers

You can use both SQL Server Enterprise Manager and the system-stored procedures to gather information about linked servers and the referenced datasources. Following are some of the most-often used system-stored procedures:

• sp_linkedservers returns a list of linked servers that are defined on the local server.

• sp_catalogs displays a list of catalogs and descriptions for a specified linked server.

• sp_indexes shows index information for a specified remote table.

• sp_primarykeys returns the primary key columns for the specified table.

• sp_foreignkeys lists the foreign keys that are defined for the remote table.

• sp_tables_ex displays table information from the linked server.

• sp_columns_ex returns column information for all columns or a specified column for a remote table.

• sp_helplinkedsrvlogin displays the linked server login mappings for each linked server.

For example, during query prototyping time, it is useful to see all of the ways that the linked server objects and columns are being referenced ( especially when dealing with other datasources, such as Excel spreadsheets).

First, the exact linked object name is displayed via the sp_tables_ex system-stored procedure. The following is what you would see for the ExcelSW linked server just created:

 EXEC sp_tables_ex 'ExcelSW'  go Result set of: Table_catalog Table_schema Table_Name    Table_Type Remarks ------------- ------------ ------------- ---------- ------- NULL          NULL         SWCustomers$  TABLE      NULL 

Then you can see all of the table columns of that linked server's datasource by using the sp_columns_ex system-stored procedure. The following command provides the column definitions for the SWCustomers$ table for the linked server 'ExcelSW' :

 EXEC sp_columns_ex 'ExcelSW'  go Table_catalog Table_schema Table_Name   Column_Name  DataType TypeName ... ------------- ------------ ------------ ------------ -------- -------- ---- --- NULL          NULL         SWCustomers$ CustomerID   -9       VarChar  255  510 NULL          NULL         SWCustomers$ CompanyName  -9       VarChar  255  510 NULL          NULL         SWCustomers$ ContactName  -9       VarChar  255  510 NULL          NULL         SWCustomers$ ContactTitle -9       VarChar  255  510 NULL          NULL         SWCustomers$ Address      -9       VarChar  255  510 NULL          NULL         SWCustomers$ City         -9       VarChar  255  510 NULL          NULL         SWCustomers$ Region       -9       VarChar  255  510 NULL          NULL         SWCustomers$ PostalCode    6       Double    15    8 NULL          NULL         SWCustomers$ Country      -9       VarChar  255  510 NULL          NULL         SWCustomers$ Phone        -9       VarChar  255  510 NULL          NULL         SWCustomers$ Fax          -9       VarChar  255  510