How to rename a column in an MSSQL temporal table
Temportal 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’ then 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 (View, Key, etc.). Remove these dependencies (dont 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 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:
ALTER TABLE DatabaseName.SchemaName.TableName SET (SYSTEM_VERSIONING = ON)
Drop me a line if these steps helped you!