Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
I'd look towards PowerShell to handle multi-instance execution. –  Thomas Stringer Apr 18 '12 at 19:52

1 Answer 1

up vote 3 down vote accepted

There's lots of options.

share|improve this answer
    
If I do use linked servers, is there anything flawed with just looping through each one and building the connection screen as above? I have been taught to fear cursors and dynamic SQL but this seems like one of those necessary evil situations. –  Alan Whited Apr 18 '12 at 20:14
    
Cursors and dSQL are fine for stuff like this as there's no other option. You are controlling the code so it shouldn't be a problem to push it out like this. You are dependent on the linked servers being setup correctly, but that's an acceptable risk in my book. –  mrdenny Apr 18 '12 at 20:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.