SQL Server: How to List the Full Properties of all Linked Servers

What is the best way to see the full properties of all of the Linked Servers in SQL Server in one go? A SQL query is naturally the best way using SQL Server’s system tables!

UI-Based Solutions:

• The properties view in the SSMS UI does not show sufficient information about the set-up and history of the linked server.
• In the SSMS UI one can click on the facets option rather than the properties view, which shows slightly more data.
• One can script the linked server, which will show the important set-up information, but is generally not very friendly to read.

T-SQL Solutions:

The most thorough (and easy) method to check  configuration information and modification information for a linked server is to query the system tables.

To generate a summary report of the Linked Servers in SSMS, run the sp_linkedservers stored procedure:

exec sp_linkedservers

To generate a detailed  summary report of the Linked Servers in SSMS, run the following query:

SELECT *
FROM sys.Servers a
LEFT OUTER JOIN sys.linked_logins b ON b.server_id = a.server_id
LEFT OUTER JOIN sys.server_principals c ON c.principal_id = b.local_principal_id

The sys.Servers table has a row per linked or remote server including a row for itself, while the sys.linked_logins returns the login mapping for each linked server. The sys.server_principals table then returns the server-level principal information for each linked server

Conclusion:

There are several methods for viewing the details of linked servers in SQL Server. One can use the SSMS UI to view properties or view facets, or one can even read through the scripted linked server.

The easiest method to view the details for one or all linked servers is the query the SQL Server system tables. Rather than viewing limited information or having to manually view the set-up code for each linked server, a query to the system tables will show amazing detail of all linked servers in one go.

Resources:

• Microsoft Support Documentation of  the sp_linkedservers stored procedure
• MSDN Documentation of the sys.Servers table
• MSDN Documentation of the sys.linked_logins table
• MSDN Documentation of the sys.server_principals table