Linked Servers

Categories: News, Professional, Scripts, SSC, SSSOLV
Comments: 1 Comment
Published on: March 1, 2012

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.

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.


1 Comment - Leave a comment
  1. […] This is a follow up to an article published on 3/1/2012.  That article showed how to find what linked servers were created on your instance of SQL Server.  You can read it here. […]

Leave a comment

Your email address will not be published. Required fields are marked *

March 2012
« Feb   Apr »


  • @mrdenny: @briancarrig depends. Treat them like a table for this discussion. Mostly it depends on your storage capabilities. #sqlhelp
  • @tjaybelt: #sqlhelp I need a hadoop hdinsight expert that can help me troubleshoot some immediate issues i am having. We can pay.
  • @SQLKohai: Am I missing a cool SSDT plugin or is it impossible to get an full error except by copying the text and pasting into notepad? #sqlhelp
  • @SQLSoldier: @briancarrig Same as a table. Only if workload/activity warrants it. #sqlhelp
  • @briancarrig: Is a dedicated filegroup for service broker queues a recommended best practice? #sqlhelp
  • @mrdenny: @whoisyarad you mean to remove a column from a table? ALTER TABLE MOCK_DATA DROP COLUMN {ColumnName}; #sqlhelp
  • @whoisyarad: How do i delete column within SQL? SELECT * FROM Practice.dbo.MOCK_DATA Then Replaced the Select with delete #sqlhelp
  • @SQLHA: @kleegeek It's pretty hard to do that. I have yet to find a consistent way. #sqlhelp
  • @kleegeek: Looking for a reliable way to trigger a BSOD on Win2012R2 for some availability testing. Any ideas? #sqlhelp
  • @SqlrUs: @SQLChao Password manager/generator with complex passwords. #sqlhelp

Welcome , today is Monday, November 30, 2015