I have built a database project within SSDT for Visual Studio 2017. I have also created Publish profiles that succesfully deploy to our dev, QA, staging, and production databases with no issue, including proper Pre/PostDeployment scripts.
The issue that I now face is that I need to "dynamically" publish the database project to 300+ databases. By dynamically, I mean that each of the 300+ databases are relatively out of sync. For instance, I have objects that I want to drop in database ABC that might not necessarily be in XYZ. I'll also have objects in XYZ that I want to drop that aren't in ABC.
The objects I want to keep are all in source control, so I am using the Publish option to
Drop objects in target but not in source. There might be table definitions that slightly differ from one database to the next, especially with regards to indexes and constraints, or temporary backup tables that were created during account specific investigations that should be dropped.
Because of these inconsistencies across the schemas, an approach to build the project once and deploy that build script to all databases won't work as it might miss objects that need to be dropped or recreated in database XYZ. The goal is to essentially sync all of these databases to the same schema.
I have read a few articles that delve into utilizing batch scripts, but they all seem to cover the case of building the database build script once and deploying that single script across all databases.
I have not yet found a method that will build and publish the project for each of the 300+ databases individually. I understand that this is likely to be a rather long deployment process, but given the inconsistencies throughout all the schemas due to mixed deployment procedures prior to my coming on with the company, we need to get the schema to a state that is consistent across the board.
We are using Visual Studio Team Services for our TFS repository, so I believe there may also be an option to create a Build Definition workflow in this scope. But alas, there seems to be relatively minimal information on this use case. PowerShell calls to SQLCMD might also be another option, but I have little to zero experience with this.