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 »


  • @matt40k: Can DacFX create a sqlproj or just a .dacpac? #sqlhelp
  • @DaniSQL: Anyone know of a book/blog/white paper etc. on how a well run DBA team and (SQL Server)database infrastructure should look like? #sqlhelp
  • @DBA_ANDY: Making copy of table w/o PERSISTED column brings CHECKTABLE back down to "normal" - this fixes it but why is it happening? #sqlhelp (3/3)
  • @DBA_ANDY: at but it talks about indexes on computed columns - I don't have any indexes/stats on the columns #sqlhelp (2/3)
  • @DBA_ANDY: Interesting problem - adding computed column takes CHECKTABLE duration to infinity & beyond - found @PaulRandal blog related #sqlhelp (1/3)
  • @capnsue: @SQLSoldier "db is using simple recovery model, not possible to restore subset of read/write data" :(but the entire DB is readonly! #sqlhelp
  • @niteshsrivast10: #sqlhelp ' nice article but how sql server internally identify the uncommitted transaction please explain #sqlhelp
  • @TossingRazors: #sqlhelp Do u trust server backup products to do SQL backups? (With their SQL plugins not just snapshot backups?) NetBackup, Veeam, etc
  • @SQLSoldier: @capnsue No, should not matter unless you are in a situation where you need to restore logs because the FGs are not in sync. #sqlhelp
  • @capnsue: @SQLSoldier awesome thank you. I'm in simple recovery mode though - does that make a difference? #sqlhelp

Welcome , today is Tuesday, October 6, 2015