How to handle SQL system generated names

bootieday

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?

Brian Donahue

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.

bootieday

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