Visual Studio DataBase Project rename a column

3
votes

I have a Visual Studio Database Project, there seems to be little and sketchy documentation on this type of Project.

The issue: I want to rename a column.

Problem: The table I want to rename the column on has data in it, so every time I generate a script I end up with this piece of code that causes the script to bomb out because there is data in the table.

 IF EXISTS (select top 1 1 from [dbo].[res_file_submission])
RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT

I have no idea how to get round this, and I really don't believe deleting this line is the answer, I have deselected the 'Block incremental deployment if data loss might occur' option, but again this seems to make no difference.

UPDATE: The column has a constraint, which seems to be the cause.

3
visual-studio-2013database-project
Have you highlighted the column and done a Refactor-Rename on it (Ctrl+R, R by default)? That should just issue an sp_rename on the column and related objects without giving you too much grief. It sounds as if your script is attempting to recreate the table or something similar. What does the actual script look like when you just choose to generate script? - Peter Schott
Just to amplify this response, when you use refactor, an entry for each change is added to the refactor.log file -- this triggers an sp_rename, and without it it will drop the old column. - bigtech

3 Answers

2
votes

You can simply rename column name in post build deployment script by using command(rename query). It will allow you to change the name of column and also not harm your data in that table.

1
votes

You can handle this via pre- and post-deployment scripts.

Create a pre-deployment script to back up the table and delete its data:

if (OBJECT_ID('TempDB..#MyTableBackup') is null)
begin
    -- backup data to a temp table
    SELECT *
    INTO #MyTableBackup
    FROM MyTable

    -- TODO: If you have foreign key constraints that reference MyTable, you'll need to disable them here.

    -- delete the data in your table
    DELETE MyTable
end

Create a post-deployment script that restores the data:

-- TODO: Only include the SET IDENTITY_INSERT lines if your table has an identity column
--SET IDENTITY_INSERT MyTable ON

INSERT MyTable
SELECT *
FROM #MyTableBackup

--SET IDENTITY_INSERT MyTable OFF

-- TODO: If you disabled foreign key constraints in the pre-deployment script, enable them here.

DROP TABLE #MyTableBackup

Since the pre-deployment script empties your table, the column rename will occur during the regular part of the deployment without getting the "Block incremental deployment..." warning.

Be sure to remove these scripts from the project after the deployment succeeds so that they are not rerun during your next deployment.

1
votes

The issue was not with my inability to rename the column, but the fact that I needed to rename the column and then deploy the rename, and the compare script kept preventing the rename.

THE SOLUTION: Rename my column in the database project including references to it, then add a script to the pre-deployment script on my target to perform the same name change. Then to run the pre-deployment script on my Database to update it, then run the compare, which will not now include the column rename as both the target and the source have the same name.