How do I drop system versioned tables in a SQL Server database project

Question

We're using a .NET SQL Server database project to define our database, and it's not deleting tables from the server even though we have deleted them from the database project.

There is an option in the publish profile to drop objects that are in the target but not in the source. However, this doesn't work for temporal tables as I get an error saying it can’t drop temporal tables as the standard SQL drop command is not supported on the temporal table.

Is there a way to drop temporal tables using a SQL Server database project?

Answer 1

Temporal Tables are system tables; you cannot drop them directly. First, turn off system versioning.

Your query will be like this.

ALTER TABLE <TEMPRAL_TABLE_NAME> SET ( SYSTEM_VERSIONING = OFF)
GO
DROP TABLE <TEMPRAL_TABLE_NAME>