Dispersia Dispersia - 4 months ago 11
SQL Question

Include but not Delete SQL Schema Compare

I am attempting to use SQL Schema Compare in Visual Studio 2013/15 and am running into the problem that discluding tables from delete removes them from being processed at all.

The issue is that the tables it is trying to delete are customer made tables, so when we sync our version against their databases it asks to delete them. We do not want to delete them, but some of their tables have constraints on ours so when it attempts to CCDR it fails due to table constraints. Is there a way to add the table to be (re-created? like the rest of them?), without writing scripts for each client to do what SQL Schema Compare already does just for those few tables?

Red-Gate's SQL Compare does this somehow, but it's hidden from us so not quite sure how it's achieved. Discluding doesn't delete, but does not error on the script either.

UPDATE:

Another thought would be to temporarily disable the constraints at the beginning of the script, then re-enabling them, but I would prefer to not do as it takes a very long time to do it for each table (as the client can have hundreds), and disabling them all as per: How can foreign key constraints be temporarily disabled using T-SQL? causes the visual studio compare to just stop without an error.

Answer

The solution was to disable all of the constraints as per: How can foreign key constraints be temporarily disabled using T-SQL?

but in the part that reads:

exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

changing to:

exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

works. Something about the print that does not work? Not sure, but it removes constraints, updates all tables, then re-enables the constraints.

If there is a better solution, please let me know.