Executing the same SQL statements against multiple servers and databases using SQL Server Management Studio, and Merging the Results

Validated against: SQL Server: 2008 R2.

If you need to apply the same SQL statements against multiple servers and databases, this can be accomplished using SQL Server Management Studio (version 2008 onwards). Simply with the Registered Servers window, select the group of servers and perform a “New Query”. When the query is executed it will be applied against every server in the server group; concurrently.

So in the real world what are the extra steps required to make the latter actually work. Just recently I applied the steps below again over 100 databases on 15 servers. What’s more this was really easy.

Let’s say you have query that must be applied against various SQL server instances and their associated databases, however not all the associated databases. The results should be merged (union) together in one list. To do the latter I suggest the following:

1) Using SQL Server Management Studio and the Registered Server window (shortcut Ctrl+Alt+G) create a server group that contains all the servers (new server registration) you want to query against.

New Server GroupNew Server Group

New Server GroupNew Server Group


2) Against the Registered Server group, perform a right mouse click for the shortcut menu and select “New Query”:
New Query

New Query


3) Create the SQL script, one approach could be as follows:

USE [tempdb]
SET NOCOUNT ON;
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('tempdb..#Results') IS NOT NULL 
    DROP TABLE #Results;
GO	

CREATE TABLE #Results
( [Local DateTime] [DATETIME] NOT NULL ,
  [UTC DateTime] [DATETIME] NOT NULL ,
  [ServerName] [NVARCHAR](128) NOT NULL ,
  [Database] [NVARCHAR](128) NOT NULL ,
  [Result] NVARCHAR(MAX)
);
GO

EXEC sp_msforeachdb '
Use [?];
IF DB_NAME() LIKE ''%???%''
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[Names]'') AND type in (N''U''))
	INSERT INTO #Results
	([Local DateTime],[UTC DateTime],[ServerName],[Database]
	,[Result])
		SELECT  GETDATE(), GETUTCDATE(), @@SERVERNAME, DB_NAME()
		, n.[Name]
		FROM [dbo].[Names] n;
';

SELECT  [ServerName], [Database], [Local DateTime], [UTC DateTime], [Result]
FROM    #Results
ORDER BY 1, 2, 3, 4, 5;

4) As can be seen in the latter example, the SQL script tests each database in turn to see if the script is allowable on the database. This is done by testing the database name and also seeing if a table with a name of [Names] exists in the database. When the query is applied against the database, the results are placed in a temporary table. By placing the results in a temporary table, allows the results of each database within the server to be merged together.

5) The last step is to return the results in the temporary table (#Results).

6) In order to merge all the server results together, use Query Options (menu Tool | Options) and select Multiserver Results and set Merge results to true.
Merge Results

7) When the script is executed, SQL Server Management studio applies the script concurrently to each server in the server group. The results from each server are merged together which result in one result set being displayed.

This entry was posted in SQL 2008, SQL 2008 R2 and tagged . Bookmark the permalink.