CODEWITHSUNDEEP
sqlsql-serversql-server-2008
neezer
neezer

Reputation: 20560

How to apply a SQL statement to many SQL Server 2008 R2 databases?

I have 4 machines running SQL Server 2008 R2 in VirtualBox, and each of the instances has 16 databases on them, with the name ending in an incremented number (1 - 16)... so database1, database2, database3, etc.

All of these databases are clones of each other (same structure); we use them for parallel testing for a Rails project.

I need to add two columns to a given table on all 64 databases. How can I accomplish this in an automated (or at least painless) fashion? Is there a way I can write a script or something in SQL Server to do this?

Upvotes: 1

Views: 99

Answers (2)

granadaCoder
granadaCoder

Reputation: 27852

The best way to handle "run the exact same script over and over" on different servers/databases is to get familiar with the sqlcmd.exe way of doing things.

  1. Create a file call "MyBatFile.bat" in a some folder.

Put this in the file:

set __sqlcmdexe=C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE
set __sqlcmdexe=C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE




"%__sqlcmdexe%" -S Server001\Instance001-i .\MyVersionSample.sql -o MyOutput_Server001_Instance001_Northwind.txt -v MyDatabaseName="Northwind" FavoriteColor="Red" MyErrorLog="MyErrorLog001.txt"
"%__sqlcmdexe%" -S Server001\Instance001-i .\MyVersionSample.sql -o MyOutput_Server001_Instance001_DoesNotExistDB.txt -v MyDatabaseName="DoesNotExistDB" FavoriteColor="Green" MyErrorLog="MyErrorLog002.txt"


set __sqlcmdexe=

Adjust the values "Server001" and "Instance001" (instance name, if applicable) to match something on your environment. I used "Northwind" as a real db name. Put in some (real) database name that you have. Obviously, make sure you have a correct path to sqlcmd.exe on your machine.

  1. In the same directory, create a file called "MyVersionSample.sql".

Put this in it:

/*
: s e tvar MyDatabaseName "Northwind"
*/

:Error $(MyErrorLog)


Use [$(MyDatabaseName)]
GO

print 'You passed in a variable'
print '$(FavoriteColor)'
print ''

IF NOT EXISTS (SELECT name FROM master.sys.databases WHERE name = N'$(MyDatabaseName)')
BEGIN
    print 'Database does not exist!!'
    print '$(MyDatabaseName)'
    print ''    
END


Select @@VERSION


GO

This simple example does a Select @@Version. Fairly benign. But it will demonstrate.

  1. Bring up a command line. Run the file "MyBatFile.bat"....

You'll now get some log files. Maybe an error file.

Basically, it will run the same script over and over against the environment and database you setup in the bat file. And you get some log files.

The .sql itself has to be "sqlcmd"'ish. It's not alot of work.

when testing your scripts, if you go to "Query : SqlCmdMode" (where Query is a menu item like "File and Edit"....you can enter into sqlcmd mode.

Note in my example, you'll see this code.

/*
: s e tvar MyDatabaseName "Northwind"
*/

if you uncomment that and take out some spaces,to make it this:

:setvar MyDatabaseName "Northwind"

(reminder you need to be in sqlcmdMODE)..you can now test your script.

BE WARNED: If you setvar something in code, it will overwrite the value you pass in from the .bat file with the -v flag. This is documented here:

http://connect.microsoft.com/SQLServer/feedback/details/382007/in-sqlcmd-scripts-setvar-should-have-a-lower-precedence-than-command-line-variable-assignments

This is why I both commented out that part, and put some spaces in the :servar to make sure it doesn't override the values being passed in via the -v switch.

Boo to microsoft for not fixing it.

But this is the best way to do the same script over and over. with some simple log files. (IMHO).

I had a project where I had to create 24 databases (same DDL/schema) with filegroups, files, alot of stuff...........and I was able to whip them out within minutes......using this system. and logfiles for stuff that didn't go right.

This is a great way to get predictable results...and not accidentally missing something.

Good luck.

Upvotes: 1

Mahesh Sambu
Mahesh Sambu

Reputation: 349

Create an SSIS Package, use your sql command as an expression and build the expression dynamically with a variable(database name)

Run the ExecuteSQLTask using this command expression

EX: "SELECT * from "+@[User::DATABASEName]

in your case add the

"ALTER TABLE "+@[User::TableName] "ADD COLUMN1 datatype, COLUMN2 datatype"

Upvotes: 0

Related Questions