Filtered change script?

mattlavallee

Hi,

I'm trying to generate a change script (between Dev and Prod live databases) to include only column changes between 3 tables. In other words, only creating statements like:

ALTER TABLE x ADD COLUMN x bit
ALTER TABLE q ADD COLUMN w bit

etc.

The only exports or reports I seem to be able to generate all recreate the tables (CREATE TABLE statements), albeit with the changes included. What am I missing?

-Matt

David Atkinson

Hi Matt,
In the Comparison Results screen it is possible to check and uncheck the 'include' checkboxes, which will limit the generated synchronization script to the selected items only. You will find an option to select none from the menu. In future versions of SQL Compare the objects will be deselected by default so hopefully it should be more obvious that it is possible to exclude objects from the synchronization script.

Please give this a go and let us know if this solves your problem.

Kind regards,

David Atkinson
Red Gate Software

Michelle T

SQL Compare doesn't currently treat different parts of a table individually - either the entire table gets synchronized or none of it does. So if you have other changes on the tables you want to synchronize the columns for, those changes will be synchronized as well.

If you're generating a synchronization script for the tables and getting CREATE TABLE statements, this is probably because the tables have some difference which can't be synchronized just with ALTER statements so we have to rebuild it. In the 'Warnings' tab of the synchronization wizard, there should be a warning explaining why we're doing the table rebuild.

mattlavallee

Okay, maybe I'm just having a usability issue, then... I think I've figured out what's going wrong (for me), but I'm not sure there's a better approach. Just for validation, here are my steps:

• Set the two data sources, and direction from Dev to Prod.
• Hit Actions -> Exclude All.
• Select the three table changes and one added scalar-value function from the list.
• Go to Synchronization Wizard... (this is where I may be going wrong, but I can't find another way to get the change script)

Here, it is including *all* of the index and constraint differences between the tables, not just those affecting the changed columns. Of course, I don't want to drop all of my destination indexes and recreate them, particularly since none affect the column I'm concerned with moving... and I can't turn off the constraint or index options because then the changes I want to propagate wouldn't include these dependencies.

Does that make sense? Is there any way to be more granular about what gets moved at the table level, i.e., to consider the column-level differences?

Also, is there another way of getting the change script rather than going into the Wizard and viewing the source? That feels awkward, because I don't actually want to apply the change yet, we just want the SQL.

Thanks for your help,
-Matt

mattlavallee

It's worth mentioning that I say all this because the irony is that the Interactive HTML Report *does* do exactly what I expect. That is, it includes only the affected-column changes.

-Matt