Get All Linked Servers Modify Date in SQL Server

This is a short post that extends on a concept that I talked about in a previous article about querying linked server information in SQL Servers 2005, 2008, & 2012 using T-SQL. Specifically, I will give an example of how to check the last modified date for each linked server residing on your database server.

Querying Linked Server Modified Date in SSMS

The simplest method is to open SQL Server Management Studio and query the sys.Servers system table directly. In a previous example I showed how to tie in sys.linked_logins and sys.server_principals. However to simply get the modify date, you can query sys.Servers as follows:

SELECT a.name,a.product,a.modify_date
FROM sys.Servers a
WHERE server_id>0
ORDER BY
a.modify_date DESC

This query orders the linked servers in reverse chronological order by the date that each was modified.

Futhermore, the name column shows the given name of each linked server, and the product column shows the remote server that is being connected to.

As you can see, the query has a where clause that excludes server id: 0. This is because the sys.Servers System table will include a row for the local server as server id:0, which is not interesting for our purposes.

For more information, you can read a great breakdown of the sys.Servers System table and its columns on the Microsoft Technet site.