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