SQL Server Multi Database Query with Registered Servers

Problem

A user supports an application that has three databases for three different environments. These databases are located on two different SQL Servers and have different names. The user needs to validate application users on each of these databases. How can he/she run a single query in SQL Server Management Studio (SSMS) for all three databases?

Solution

In this tip we will show you how to run a query against multiple databases that are spread among different SQL Server instances. We will use the Registered Servers feature in SSMS. The same result could be achieved using a multiple servers query with Central Management Server (refer to this tip to find out how to use Central Management Server).

Pre-requisite

We will demonstrate the solution for this tip using two SQL Server instances and three databases:

Register Servers

We will create the new Server Group "Demo App1" and register our two test SQL Servers under this group (refer to this tip to find out more about how to register multiple servers in SSMS).

Register the servers under the "Demo App1" Group as follows (note that we have the same "Server name" twice, but a different "Registered server name" each time, also we have different Default database for each connection):

Server Registration for the Production Environment

Right click the "Demo App1" Server Group and click the "New Server Registration". Enter "SQLDEMO1" as Server Name and "ProductionDB" as Registered server name:

Click the "Connection Properties" tab, then "<Browse server...>" next to "Connect to database":

Click "Yes" to continue, select DemoApp_ProdDB database:

Click "Save".

Server Registration for the Test Environment

Right click the "Demo App1" Server Group and click the "New Server Registration". Enter "SQLDEMO2" as Server name and "TestDB" as Registered server name:

Click the "Connection Properties" tab, then "<Browse server...>" next to "Connect to database", select DemoApp_TestDB database:

Click "Save".

Server Registration for the QA Environment

Right click the "Demo App1" Server Group and click the "New Server Registration". Enter "SQLDEMO2" as Server name and "QADB" as Registered server name:

Click the "Connection Properties" tab, then "<Browse server...>" next to "Connect to database", select DemoApp_TestDB database:

Click "Save".

Run Multi-database Query

Under Registered Servers right click on the "Demo App1" SQL Server Group that we have created and click "New Query":

I have over 100 servers registered in my CMS. From time to time we have to validate all of those servers are operational. Is their automatically run the same query I run in CMS say through a job or some other method?

Does anybody know a way to execute the same multi-server query through T-SQL only. As in, something to the equivalent of "USE <dbname>". Or a way to open a query automatically that is connected to the registered group?

I'm pretty sure there isn't a way and the only way you can do it is to go through the registered server list, but just wondering.