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.
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.
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.