Have you ever wondered how to perform the same query on different servers without going to each of the SQL Servers? Well I was presented this scenario just a while ago because I wanted to know what versions of SQL we are running and first thing I thought of is using the command
SELECT @@Version
But my problem now is that I have to perform the query in each server which will be a tedious task as we are running 90 instances of Database servers on our organization. So what is the best way to do? Fairly simple! If you have properly registered your SQL Servers in your SQL Server Management Studio in a group then is as simple as running the query across that group. This might be an old solution for you guys but let me share what I did, it might be still useful for some other readers.
First is I have a grouped servers as in the picture
Server Groups
Now on my “PRODUCTION” Folder I right clicked and choose “New Query”, New query editor pops up and I write my query there. That simple
Query and Results
Now the handy part comes in, if you two Database Servers with the same Database name and structure that you can run the query across those same tables. For example I have 2 servers with a database named “xxxxx-security” and a same table name and structure named “tblApplication”. You can easily now use one command on both.
Same Database
as you can see from the image above it will show all the databases that are the same apart from the system databases, in our case “xxxxx-security” database.
Same Database Query Results
Now when you query the tables you will be presented with another Field Automatically which is called “Server Name”.