THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

Execute T-SQL Across Listed Servers/Databases

Here’s a handy trick – if you have a SQL script, stored as a text file, and need to run it against an arbitrary list of different databases on different SQL Server instances, try a little PowerShell. There are a lot of ways to accomplish this task, but personally I like this one for its flexibility.

First, make sure the SQL script does NOT include the common USE <database>. Generally that statement is your friend, but not in cases where a single script should work against multiple databases having different names.

Next, make a “collection of collections” in PowerShell, which will act as a list of instances and databases on those instances. Example:

$instances = @(
    @( 'Server1', 'someDatabase' ),
    @( 'Server2', 'AnotherDatabase' ),
    @( 'Server32', 'FooDB' )
    # Repeat
)

Each item in $instances is a mini-collection, and each of those collections has two elements: an instance name and a database name, which will be available using indexes [0] and [1].

If you prefer, it’s also possible to pull these values from a text file using Get-Content, and split each line on some delimiter character.

Next, load the content of your SQL script file into a variable. There’s an important caveat here: we have to load it as a single string, not an array of strings. The default behavior of Get-Content, though, is to split a file on line terminators and make each line into a separate object in a collection of strings.

There are a few ways to accomplish this, but I learned a simple one in a forum from Alex K. Angelopoulos – we can direct Get-Content to split on a character that doesn’t actually exist in the file. This makes the whole content of the file “look like” one line to Get-Content. He suggests the Null character, which in PowerShell is `0 (back tick zero). Note that the Null character is not the same or related to $null.

Special Characters: http://technet.microsoft.com/en-us/library/dd347558.aspx 

$deployScript = Get-Content 'C:\Path\To\Your\Script.sql' –Delimiter `0

Finally, loop over the list of ( instances + databases ) to run the script everywhere. Be cautious and test!

$instances | ForEach {

    # This should print the instance and database from your list, which is handy for troubleshooting:
    Write-Host $_[0] $_[1] 

    # This sort of thing can be used to validate that you are connecting to the right databases:
    Invoke-SqlCmd –ServerInstance $_[0] –Database $_[1] –Query 'Select @@servername, db_name()'

    # Finally, this would execute the script:
    Invoke-SqlCmd –ServerInstance $_[0] –Database $_[1] –Query $deployScript

}

There is no Undo, so test. Use your powers only for good!

Edit 5/5/2012 – fixed missing commas in code sample.

Published Friday, May 04, 2012 4:50 PM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

martinz said:

G'day Merrill,

Nice idea. However, when I run this on my installation of Powershell (2.0) I simply get the letter at position 0 and 1 of each array entry.

for example, running the following

$instances = @(

   @( 'Server1', 'someDatabase' )

   @( 'Server2', 'AnotherDatabase' )

   @( 'Server32', 'FooDB' )

   # Repeat

);

$instances | ForEach {

   # This should print the instance and database from your list, which is handy for troubleshooting:

   Write-Host $_[0] $_[1];

}

produces this output

S e

s o

S e

A n

S e

F o

obviously I need to tweak something - just need to find out what :)

cheers

Martin.

May 5, 2012 2:45 PM
 

merrillaldrich said:

D'oh! - That was my mistake. I missed commas in formatting for the blog. Fixing now... My apologies.

May 5, 2012 3:05 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement