In this article we will see how to check and drop a system-versioned temporal table if it exists in the database. Temporal tables are slightly different from regular tables. A temporal table is a set of two tables, a current table and a history table. So, you cannot drop a temporal table like a regular table. If you try to drop the temporal table using a drop script, you will get an error.
DROP TABLE People
/* Result */
Msg 13552, Level 16, State 1, Line 20
Drop table operation failed on table 'Test.MyTecBits.com.dbo.People'
because it is not a supported operation on system-versioned temporal tables.
To drop a temporal table, you have to follow three steps:
- Alter the current table and set off the system versioning.
- Drop the current table.
- Drop the history table.
In my earlier articles, we have seen how to check if temporal table exists and how to find the corresponding history table of a temporal table. Using these techniques, I have formulated a script to check if the given table is system-versioned temporal and if so, perform the three steps to drop the table.
Sample Script
/* Create a system-versioned temporal table */
CREATE TABLE PeopleMaster
(
PeopleID INT NOT NULL PRIMARY KEY
, FirstName VARCHAR(50) NOT NULL
, MiddleName VARCHAR(50) NOT NULL
, LastName VARCHAR(50) NOT NULL
, Address VARCHAR(250) NOT NULL
, PeriodStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
, PeriodEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
, PERIOD FOR SYSTEM_TIME (PeriodStartTime, PeriodEndTime)
)
WITH (SYSTEM_VERSIONING = ON);
GO
/*
* To check and drop the system versioned temporal table if exists
*/
DECLARE @temporalTable VARCHAR(MAX),
@historyTable VARCHAR(MAX);
SET @temporalTable = 'PeopleMaster'; /* Given table */
/* Check it the given table is a temporal */
IF EXISTS (
SELECT 1
FROM sys.tables
WHERE name = @temporalTable
AND temporal_type = 2
)
BEGIN
/* Find the history table */
SELECT @historyTable = OBJECT_NAME(history_table_id)
FROM sys.tables WHERE name = @temporalTable;
/* If yes alter the current table and switch off system versioning */
EXEC('ALTER TABLE [dbo].[' + @temporalTable + '] SET (SYSTEM_VERSIONING = OFF)');
/* Drop the current table */
EXEC('DROP TABLE [dbo].[' + @temporalTable + ']');
/* Drop the history table */
EXEC('DROP TABLE [dbo].[' + @historyTable + ']');
END
GO
Reference
- More about temporal tables at Microsoft Docs.