Database, SQL, SQL Server 2005, SQL Server 2008, SQL Tips, System Objects, Transact-SQL, tSQL

Get All Linked Servers Modify Date in SQL Server

Microsof SQL Server
Microsof 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:

FROM sys.Servers a
WHERE server_id>0
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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s