How to rename a column in an MSSQL temporal table

Temporal tables is a neat feature that allows us to version the data of our MSSQL-tables so that we keep a history of changes made. The renaming of columns of a table which is backed up by a temporal table is not 100% straightforward since you do not get the “Design” option when you right click on the table in SQL Management Studio. To perform the renaming, do the following steps:

• Temporary deactivate the temporal table by executing the following query:

    ALTER TABLE DatabaseName.SchemaName.TableName SET (SYSTEM_VERSIONING = OFF)
  

• If the query fails with the following message

    Cannot ALTER 'TableName' because it is being referenced by object 'XXX'
  

check for dependencies that the table has to other resources. For that, right click on the table and then click on View dependencies. You get a list with the dependencies with their types, most of the times you will have to remove the Foreign Keys in other tables to your temporal table, or other Views are using columns from your temporal table. Remove these dependencies (do not forget to keep the CREATE queries to re-create them, after renaming the column) and run the query from step 1 again.

• Rename the column (in normal and history table!) by using the following query (the first parameter is the name of the old column and the second the new name):

    EXEC sp_rename 'DatabaseName.SchemaName.TableName.ColumnName', 'NewColumnName', 'COLUMN';
  

• Activate the temporal table again (pay attention on writing the correct name of the history table, without the Database name):

    ALTER TABLE DatabaseName.SchemaName.TableName SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = SchemaName.HistoryTableName))
  

Refresh the Database and check if the renamed column is available on both tables. Drop me a line if these steps helped you!