Is there a way to automate Compare against many databases?

MKZeke

Hello,

What I am looking to do is run a single master database against an array of division databases in order to add any tables, roles, stored procedures, and views that are missing from each of the division databases in an automated fashion. The master database is set up on Team Foundation Server via SQL Source Control, and I was looking to have it so that if the master database was updated to have another table, view, etc., then it could run through each division database and add that object to all databases that did not have it added.

I am aware of using Compare's Command Line to make a batch file with multiple commands, but I do not wish to manually enter in a command for each database.
Here is an example of what I mean to do through Command Line:

SQLCompare /sourcecontrol1 /revision1:HEAD /s2:[server] /Database2:[database] /userName2:[Username]/password2:[password] /sfx:[location] /include:Missing /include:Role /include:StoredProcedure /include:Table /include:View

Is there a way to run an array of different databases through a command line like this, and if not, is there another way I could go about what I'm looking to accomplish without manually doing so?

Thanks!
--Zach

Andrew P

Hey mkzeke,

Thanks for contacting us!
The best way to do this is using a PowerShell script where your would input the database/server names into a variable as a comma separated list and generate the command line syntax foreach name on that list and have it execute each statement in the Powershell script.

Here is a good article on setting that up https://www.simple-talk.com/blogs/2014/ ... mand-line/

Kind Regards,

MKZeke

Thanks a lot Andrew, the article you provided helped a ton and everything is up and running now!