How to handle SQL system generated names

bootiedaybootieday Posts: 18
edited April 25, 2014 6:04PM in SQL Compare Previous Versions
Here is the scenario I have to solve. I'm trying to create a rollback script using SQL Compare that can be applied generically to any database on a specified product version. The tool works great to generate the original script. But my database used to model the script has SQL system names on DEFAULT constraints. So if my rollback script has to drop a constraint on rollback, it embeds the contraint by name (i.e. DF__Foo__IsDeleted__1B220F1A). Trying to execute this script on any other database obviously fails. Is it possible to do what I need using the tool and without the need to made manual modifications to the generated script?

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    SQL Compare is not designed to create "generic" scripts - the script is meant to be run only on the databases that were originally compared.

    You can try setting up the script so it will not roll back on failure (Do not use transactions), or use the "check object existence" option. Either could get the script to run successfully, ignoring any objects that it cannot modify. You could get database inconsistencies doing it this way.

    The best solution would still be to run the comparison between all databases rather than doing one comparison and trying to run the synchronization scripts on databases that were not involved in the comparison.
  • I was able to work around this by replacing the specific named constraint drop blocks with a pattern like this, if anybody is interested.

    DECLARE @ConstraintName NVARCHAR(200)
    SELECT
    @ConstraintName = Name
    FROM
    SYS.DEFAULT_CONSTRAINTS
    WHERE
    PARENT_OBJECT_ID = OBJECT_ID(N'<tablename>')
    AND PARENT_COLUMN_ID =
    (
    SELECT
    column_id
    FROM
    sys.columns
    WHERE
    NAME = N'<columnname>'
    AND object_id = OBJECT_ID(N'<tablename>')
    )

    IF @ConstraintName IS NOT NULL
    BEGIN
    EXEC('ALTER TABLE <tablename> DROP CONSTRAINT ' + @ConstraintName)
    END
Sign In or Register to comment.