What are the challenges you face when working across database platforms? Take the survey
Options

Filtered change script?

mattlavalleemattlavallee Posts: 4
edited January 2, 2009 9:12AM in SQL Compare Previous Versions
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

Comments

  • Options
    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
    David Atkinson
    Product Manager
    Redgate Software
  • Options
    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.
    Software Developer
    Redgate Software
  • Options
    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
  • Options
    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
Sign In or Register to comment.