Check and drop system versioned temporal table if exists

Check and drop system-versioned temporal table if exists

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:

  1. Alter the current table and set off the system versioning.
  2. Drop the current table.
  3. 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
Check and drop system versioned temporal table if exists

Reference


Related Articles & Tools

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.