4

I'm looking to have Visual Studio's SQL Schema Compare generate the delta SQL to update a production database, including a column rename on a table with system versioning on (temporal table).

Using refactoring within Visual Studio SQL Project will indeed create an entry in the refactorlog file, and will generate the correct sp_rename SQL for that renamed field. However, the associated history table doesn't get the field renamed - it does a drop and add, which will not work well once there is actual data in the table. Plus it also makes it so the tables get out of sync with each other.

Maybe I could modify the refactorlog XML to indicate it is a field with a history table attached? the XML below shows the Elementtype is "SqlSimpleColumn". Are there any other options I could explore?

System: Visual Studio Community 2017 SQL server: Azure SQL Database V12

  <Operation Name="Rename Refactor" Key="48b6ef58-988b-48bf-9606-4048b0c51bf2" ChangeDateTime="04/03/2017 21:19:32">
    <Property Name="ElementName" Value="[dbo].[xyz2].[newafterreset]" />
    <Property Name="ElementType" Value="SqlSimpleColumn" />
    <Property Name="ParentElementName" Value="[dbo].[xyz2]" />
    <Property Name="ParentElementType" Value="SqlTable" />
    <Property Name="NewName" Value="[updateafterreset]" />
  </Operation>
JWalter
  • 71
  • 6

2 Answers2

0

Manually using sp_rename on a system-versioned history table results in the following error:

Msg 13759, Level 16, State 1, Procedure sp_rename, Line 316
Renaming a column failed on history table '<database name>.dbo.<table name>History' because it is not a supported operation on system-versioned tables.
Consider setting SYSTEM_VERSIONING to OFF and trying again.

Is it possible for you to do what it recommends?

rhefley
  • 1
  • 1
0

Yes, you can set the system versioning off before making changes to the table. ALTER TABLE [dbo].[TABLE_NAME] SET ( SYSTEM_VERSIONING = OFF ) GO

This will allow you to make changes to the table. BEWARE: A word of caution - when you execute a sp_rename to rename a column, on a system versioned (temporal) table it breaks the version history for that entity

Ranganathan Iyengar
  • 1