Incorrect schema change choice to rename a column.
I have a table called myTable that originally had an INT column called AccountID
There was a foreign key to the Account table.
I made a schema change to Drop the AccountID column and add a CountryID INT column.
I also created a foreign key to the Country table.
I took a snapshot of the new schema and tried to apply it to a different database.
When I ran the schema comparison to apply the update to a different database, I received a foreign key exception.
When I reviewed the TSQL that SQL Compare had chosen to make my schema changes I found the following line.
EXEC sp_rename N'[dbo].[myTable].[AccountID]', N'CountryID', 'COLUMN'
Because the AccountID column originally had AccountIDs in it and not CountryIDs I received the foreign key error when the rename was attempted.
The question is, why SQL Compare chose a rename operation rather than dropping AccountID and adding CountryID which I feel is really the only sane decision the API could make?