Instead of column rename the project does column drop and column add

Question

I'm using SSDT to keep 2 databases synchronized.

To do so, I have a database in Server1, a database in Server2 and an Schema in my project.

I always make changes in Server1 database, and apply those changes in Server2 database. To do so, I use this workflow:

• Compare Server1 database to Schema, and update the Schema with Server1 changes
• Compare Schema to Server2 database, and update changes from the Schema to the database

This usually works fine, but I've found a problem the last time I've renames columns in a table.

Usually, if I rename columns in a table, the change is dectected as a column rename, so, when I compare the Server1 to the Schema, the column renames are correctly detected, and I can safely finish my work flow.

However, the last time that I have renamed columns in a table in Server1, when comparing it to the Schema, instead of detecting the change as a column rename, it has detected the change as a drop column (with old name) and create column (with new name). Obviously, if I apply those changes in Server2 database I'll lose all the data in the renamed column.

Is there any reason for this behaviour in SSDT? Can I instruct SSDT to understand that this is a column rename?

I know how to do it by hand, but I'd prefer to avoid this problem in SSDT, or be able to solve it, if it appears again in the future.

Answer 1

I know this is an old question but just to clarify for those that are still finding this and can't figure out how to do the refactor steps.

While in the design view of Visual Studio, click on the column name that you want to change then click the "SQL" option in the top Visual studio menu. Then click "Refactor" > "Rename": Image with the menu option highlighted

Next you can right click on the project itself within the Solution Explorer window and choose the "Schema Compare..." option.