Kevin Hill shows how to perform schema comparison across multiple databases concurrently:
I recently had the need to compare a “Gold” or “Master” copy of our database to the 300 client copies of that database that exist in our Production environment. I’m not alone…many fellow DBAs have had the same need. Google searches for this confirm it. This is for an upcoming upgrade to the application that will need post-upgrade comparison/verification.
There are 3rd party tools that do SQL Compares…my particular favorite is aptly named SQL Compare from Red Gate. I’ve been using it off an on for 10 years. I don’t know if it can be set up to hit more than one database at a time. The other issue is that I don’t have a copy here.
Microsoft’s SQL Server Data Tools will also do this within Visual Studio. Still one database at a time. I forget where, but someone pointed me to the fact that SSDT uses SQLPackage.exe under the hood to do the work. I figure if I can run it at a command line I can script out all of the databases. I’m not much of a DOS scripting guy, so everything that follows is just my hack version…but it works, and not just on my machine!
This is very useful if you work in an environment with multiple copies of databases—or even if you have dev, test, and prod versions of the same database.