Can SQL Compare check for the existence of tables even though they don't exist in the source?

Pete6627

So the title might sound a little strange and I'll try to make this as clear as I can.

My scenario is that I'm trying to create an upgrade script to take an application up to the next version.
We have a release backup of the database that is used as the target for SQL compare and we have a dev database which is used as the source in order to upgrade the old database to include the new changes. This all works fine until you then consider that we sometimes have bug fixes, which are released to customers that add things like new tables and foreign keys. These aren't always installed right away by everyone though so you sometimes end up with customers being behind but wanting to jump to the next major version. I'll give an example of the problem I'm having although I feel like there isn't a way around this.

Let's say we have Version 1 of the app which has Table 1 and Table 2.
We've already released V1 Hotfix 1 which adds Table 3 and applies a foreign key for table 3 to table 1.

We now look at V2 of the app which changes a column in Table 1, it also includes table 3 and the foreign key.

SQL Compare comes along and scripts a drop for table 1, not knowing that Table 3 and it's foreign key might already exist (understandable - it isn't magic). Unfortunately, this means that any customer who runs this upgrade script and already has hotfix 1 installed, will have a failure when the script tries to make changes to table 1.

I guess the question is, are there any options or tricks I can deploy to work around this scenario? The script generated needs to work for multiple customers rather than just a single case and although all of the possible changes already exist in the new version of the app, customers could be in an upgrade state anywhere from RTM through to hotfix 60/70/100 etc (There is no way to know how many fixes might be released between major updates).

Can I tell SQL compare to add existence checks for the new tables even when they don't already exist in the target but do in the source?
Am I just being stupid? I hope so haha.

Any help at all is appreciated.

Dan_J

Hi @Pete6627,

Thanks for reaching out to us on this. You're certainly not being stupid.

Enabling the 'Add object existence checks' options (Edit Project ->Options tab ->Behavior section) will enable you to achieve what you're describing.

For clarity; when this option is enabled, SQL Compare will check for the existence of objects affected by the deployment. If the object is already in the target it does not create the object.  If the object does not exist, the query returns null and creates the object. This is particularly useful if you wish to create a deploy script and run it against multiple targets.

I hope this helps!