Execute SQL Queries Across Multiple Servers

Problem:

There are times when I have to find a database or a object across thousands of databases spread across hundreds of server?

Solution:

Using 2008 client you can group the servers and run a single query across many different servers. If you need to find more details on a particular server like login name or object name, you can use a cursor(code is mentioned below) to loop through each database to get the details

Step I:

This feature is enabled starting from SQL Server 2008 client. First register the servers in group in sql 2008 client. As you can in below image, I have a group called ‘Local Server Group’ and my two sql servers MyServer1 and MyServer2 are registered as part of this group. Now I can right click on the group name and run a query across all the instances registered in this group.

Query_Tool

You can also use attached cursor to get more details on each database.

This is a very simple and easy way to run scripts across different servers. You can also use Red Gate’s SQL Multiscript which also does an excellent job in running script across different servers, this tool is very handy in cases where you have to make a change to code which is consistent across many different servers.

Download Cursor Script

Leave a Reply