Linked Servers

Do you have linked servers that have been created in your environment?  Do you know what these linked servers are?

Finding Linked Servers

SQL Server has provided us means to be able to find the linked servers on each installed instance.  I will show two simple means (that don’t involve traversing the SSMS GUI) that will list the linked servers.

Both methods are very similar in nature.  I will leave it up to you to determine which you prefer to use.

Stored Procedure

Microsoft has shipped a stored procedure called sp_linkedservers.  Execute this stored procedure and you will get a list of linked servers and the details related to those objects.  One problem with this method is that not all the results returned by this proc truly represent linked servers.  This procedure will also return the name of the instance to which you are also connected.

Catalog Query

This is really what the stored procedure is doing.  But here is a modified query to return only those servers that are linked servers in the catalog.

select SRV_NAME = srv.name,
        SRV_PROVIDERNAME    = srv.provider,
        SRV_PRODUCT         = srv.product,
        SRV_DATASOURCE      = srv.data_source,
        SRV_PROVIDERSTRING  = srv.provider_string,
        SRV_LOCATION        = srv.location,
        SRV_CAT             = srv.catalog
From sys.servers srv
Where is_linked = 1

The main difference being the where condition.  Querying the sys.servers catalog with a condition of is_linked = 1 will return only those servers that are linked servers.  Getting this information is a pre-requisite for an upcoming post.  I hope you find this information useful.