Script to query multiple instances

Question

SQL Server - What is the best method to run the same query over multiple databases on multiple instances? for example we have many application databases, with the same schema that need to have information aggregated.

I currently use a script that relies on linked servers already being setup and a table that identifies which instance and database to loop through.

        /* declare some cursor variables and the dynamic sql string variable */
    DECLARE @currentDB          nvarchar(64)
    DECLARE @connectionstring   nvarchar(256)
    DECLARE @sqlstring          nvarchar(max)


CREATE TABLE #TEMP1
                (
                     currentDB      varchar(32)
                    ,field1         varchar(128)
                    ,field2         smallint
                    ,field3         datetime
                )

/* Build and open the cursor */
DECLARE connectioncursor CURSOR FAST_FORWARD
FOR
    SELECT  currentDB, connectionstring
    FROM    [admin].dbo.DatabaseList
    WHERE   dbtype = 'PROD'
OPEN    connectioncursor
FETCH NEXT FROM connectioncursor INTO @currentDB, @connectionstring

/* Start the loop through db list */
WHILE @@FETCH_STATUS = 0
    BEGIN
        /* Build and set the sql string */
        SET @sqlstring =
            '
                select
                         ''' + @currentDB + '''
                        ,field1, field2, field3
                        from ' + @connectionstring + '.dbo.table1
            '

        /* Insert the results from the iteration and fetch the next db */
        INSERT INTO #TEMP1 exec sp_executesql @sqlstring
        FETCH NEXT FROM connectioncursor INTO @currentDB, @connectionstring
    END

/* Kill the cursor */
CLOSE connectioncursor
DEALLOCATE connectioncursor

select * from #TEMP1 -- some output

Be gentle I'm a TSQL newbie!

Answer 1

There's lots of options.

• PowerShell
• SSMS's Multi Server Query Functionality
• Linked Servers
• Batch File with a loop and sqlcmd variables