VS 2010 Database Project Resets Column Name Change Data

Question

I am testing new features of the Visual Studio 2010 database project and want to change the column name in the table. I changed the name in create script and deployed it to the database. The created script simply deleted the column and added a new column with the correct name, but all data was lost.

Is there a parameter that will not delete column data?

I am looking for a "DataDude" solution for this problem. (If there is)

PRINT N'Altering [dbo].[Users]...'; GO ALTER TABLE [dbo].[Users] DROP COLUMN [TestX]; GO ALTER TABLE [dbo].[Users] ADD [Testn] NVARCHAR (50) NULL; GO 

Thanks Kit

Answer 1

Use the schema view by clicking View → Database Schema Schema

Expand tables and columns.

Right click on the column and click Refactor -> Rename ...

Change the name in the New Name box with the View Changes check box.

Note that this changes not only the column name, but also stored procedures that can reference this column.

Inside the database project, a refactorlog file is created that shows the name change.

When a new schema is deployed against an existing database, it looks like DataDude is looking for the refactorlog file and the dbo._Refactorlog table to determine which refactors should be processed in the database.

Here is the code generated by this procedure, change the column name that was also specified in the stored procedure:

 EXECUTE sp_rename @objname = N'[dbo].[Users].[TestF]', @newname = N'TestG', @objtype = N'COLUMN'; GO PRINT N'Altering [dbo].[ListUsers]...'; GO ALTER PROCEDURE [dbo].[ListUsers] AS SELECT [ID], [FirstName], [LastName], [TestG] FROM Users RETURN 0 GO 

Whale

Answer 2

If the table is small, you can create a new table with the correct columns, and then insert from the old table into the new one, changing the columns along the way.

If the table is large and you cannot afford the time, RAM or effort to copy the whole table, you can alter table add new_column then update table set new_column=old_column then alter table drop column old_column .

The syntax is, of course, simplified.

Answer 3

The way Visual Studio Database Projects handles schema changes lends itself to such confusion. It is very difficult for VS to tell if you simply renamed the column (and saved the data) or deleted the column and added another. My company has been working for a long time on a database schema source code management product that has all the same problems.

In the end, I came to the conclusion that the best way to handle schema changes is K. Scott Allen's suggestion (Jeff Atwood link) . In this series of articles, you should point out the best solution for version control of databases.