Combine Multiple SQL Server Query Results with Registered Servers

By:   |   Comments (3)   |   Related: More > Central Management Servers


If you manage many SQL Servers, often you may want to run the same query on multiple servers and combine the results. One way to take advantage of this is via Registered Servers in SQL Server Management Studio (SSMS). In SSMS Registered Servers is specific to the user’s individual machine and does not depend on a designated instance of SQL Servers.


When managing many Microsoft SQL Servers, the use of Registered Servers is a time saver! For one, it provides a list of all your servers for which you have an option for saving credential. You can simply click on the server name to connect to it. Also, you can save your credentials thus eliminate the need to go through login prompts when connecting to servers. You can create multiple functional groups of servers such as a Production, Development Servers, QA Servers. You can set up groups of Audit Servers or a group for the dreaded old SQL Versions. Once you have these group defined, and the servers registered you can run queries across all the SQL Servers in the group and have your results consolidated. Let’s see how.

First Create a New Server Group in SSMS

In SQL Server Management Studio (SSMS), open Registered Servers from the View menu or by combo keys: Ctrl + Alt + G. Right click on Local Server Groups and select "New Server Groupů"

registered servers new server group

Give your Group a name.

registered servers new server group properties

Register a SQL Server in Management Studio

Back in the Registered Servers pane, right click the Group name you just created. Select New Server Registration...

registered servers new server registration

Edit Server Registration in SQL Server Management Studio

Enter a SQL Server name and authentication. Optionally change the Registered Server Name to a Server name that you want Displayed in the Registered Servers list. Also, you can Add a description that will be seen if you hover over the Registered Server name.

registered servers new server registration

Expand your Server Group

Here you will see the Servers that you registered. Be sure to add a few more serves as you see I have added 2 others. You can double click on the individual server names that you registered to connect to them in the Object Explorer window. If you saved the Credentials you auto connect!

server groups list

Run a Multi-Server Query in SSMS

To run a query across all the servers in the group and have the results consolidated, click to highlight the Group in Registered Servers. Click "New Query" in the menu. A new query window will open and you can see at the bottom of the windows that SSMS was able to connect, in my example, to 3 out of the 3 servers from my registration group.

run query using registered servers

Run a Query with Results Consolidated

Start by running a simple query. Walla! The results from each server are consolidated and the Registered Server name is the first column.

run query using registered servers

Note that the queries will run in the default database for the login. It is a good idea to include USE master; in your queries if targeting the master database. I did not because the login I used for my Azure database has limited access.

Run multiple Queries against Multiple Servers

Run 3 different queries and see what happens.

run query using registered servers

Pretty cool, SSMS consolidates the 3 result sets separately!

Control How SSMS Handles the Results

In SSMS from the menu click Tools, then Options. In the Options pane, go to Query Results, SQL Server, Multiserver Results. Here you can control the functionality of the Multiserver Query results.

multiserver results configuration
Solution - Wrap Up

Imagine the benefit to Registered Servers especially when managing many SQL Servers. I have used Registered Servers to effectively manage up 100s of SQL Servers organized in logical groups. This is a huge time saver when trying to manage inventory or pull information from all your servers. It surprises me that more people do not use Registered Servers. I often export my registered servers and share with my team. Hope you enjoy this tip. If so, please leave a comment.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jim Evans Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, October 26, 2021 - 3:02:28 PM - Randy Davis Back To Top (89364)
Hi Jim. Great article. I discovered this "feature" several months ago and it has been a game changer! I'm wondering if you know whether it's possible to group by a column and have data from different servers merge together. Currently, everything returns grouped by Server, then by my group by columns.

Monday, August 16, 2021 - 7:21:29 PM - J. Jaya Back To Top (89133)
Hi - Is a New Query the only option? Is there a way to open a saved query to be executed against a registered server group?

----- My current method is tedious and hoping for something simpler as I use these queries against in 4 isolated domains (DEV, QA, STAGE, PROD): Open saved query in a non-registered query window. Copy script. Open New Registered Query window. Paste text to New Query window. Save Registered query changes using initial query's name. Thanks for your insight.

Monday, March 29, 2021 - 9:00:00 AM - Ricardo Cardoso Back To Top (88466)
Hi Jim. Great article! My question is: Is there a way to automate the results collect from CMS queries? A job that runs CMS T-SQL and put it into tables would be great. Thanks in advance for your help.

get free sql tips
agree to terms