THE SQL Server Blog Spot on the Web

Welcome to - 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: 

$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



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 :)



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


This Blog


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