If you find yourself needing to use Transact-SQL to get a list of all linked servers in SQL Server, below are two ways you can go about doing this.
In the first example I use the sp_linkedservers system stored procedure to return the linked servers. In the second example I use the sys.servers system catalog view.
Example 1 – The sp_linkedservers Stored Procedure
The sp_linkedservers system stored procedure is designed specifically for returning a list of linked servers defined in the local server.
To execute it, do this:
EXEC sp_linkedservers;
Result:
+--------------+--------------------+---------------+------------------+----------------------+----------------+-----------+ | SRV_NAME | SRV_PROVIDERNAME | SRV_PRODUCT | SRV_DATASOURCE | SRV_PROVIDERSTRING | SRV_LOCATION | SRV_CAT | |--------------+--------------------+---------------+------------------+----------------------+----------------+-----------| | c1b060f68fcb | SQLNCLI | SQL Server | c1b060f68fcb | NULL | NULL | NULL | | Homer | SQLNCLI | | 172.17.0.2,1433 | NULL | NULL | NULL | +--------------+--------------------+---------------+------------------+----------------------+----------------+-----------+
In this case I get two rows. Actually, the first row is my local server. The local server has a linked server called “Homer”, and that is displayed in the second row.
Example 2 – The sys.servers System View
The sys.servers system catalog view contains a row per linked or remote server registered, and a row for the local server that has a server_id of 0.
This view returns quite a few columns, so I’ll use vertical output to display the results in this example.
Example:
SELECT * FROM sys.servers;
Result (using vertical output):
-[ RECORD 1 ]------------------------- server_id | 0 name | c1b060f68fcb product | SQL Server provider | SQLNCLI data_source | c1b060f68fcb location | NULL provider_string | NULL catalog | NULL connect_timeout | 0 query_timeout | 0 is_linked | 0 is_remote_login_enabled | 1 is_rpc_out_enabled | 1 is_data_access_enabled | 0 is_collation_compatible | 0 uses_remote_collation | 1 collation_name | NULL lazy_schema_validation | 0 is_system | 0 is_publisher | 0 is_subscriber | 0 is_distributor | 0 is_nonsql_subscriber | 0 is_remote_proc_transaction_promotion_enabled | 0 modify_date | 2019-09-27 00:30:06.820 is_rda_server | 0 -[ RECORD 2 ]------------------------- server_id | 1 name | Homer product | provider | SQLNCLI data_source | 172.17.0.2,1433 location | NULL provider_string | NULL catalog | NULL connect_timeout | 0 query_timeout | 0 is_linked | 1 is_remote_login_enabled | 0 is_rpc_out_enabled | 0 is_data_access_enabled | 1 is_collation_compatible | 0 uses_remote_collation | 1 collation_name | NULL lazy_schema_validation | 0 is_system | 0 is_publisher | 0 is_subscriber | 0 is_distributor | 0 is_nonsql_subscriber | 0 is_remote_proc_transaction_promotion_enabled | 1 modify_date | 2019-09-29 10:31:36.570 is_rda_server | 0
You get a lot more information with the view.
Of course, you can also specify only those columns that you’re interested in.
For example:
SELECT name, provider, data_source FROM sys.servers;
Result:
+--------------+------------+-----------------+ | name | provider | data_source | |--------------+------------+-----------------| | c1b060f68fcb | SQLNCLI | c1b060f68fcb | | Homer | SQLNCLI | 172.17.0.2,1433 | +--------------+------------+-----------------+
And if you don’t want the local server returned, you can add WHERE is_linked = 1 to your query:
SELECT name, provider, data_source FROM sys.servers WHERE is_linked = 1;
Result:
+--------+------------+-----------------+ | name | provider | data_source | |--------+------------+-----------------| | Homer | SQLNCLI | 172.17.0.2,1433 | +--------+------------+-----------------+