How do you drop a temporal table in SQL Server?

Question

I'm trying to drop a temporal (system-versioned) table in SQL Server. I used a regular DROP TABLE statement.

DROP TABLE [schema].[table]
GO

This raised the following error:

Drop table operation failed on table '[database].[schema].[table]' because it is not a supported operation on system-versioned temporal tables.

How do you drop a temporal table in SQL Server?

Answer 1

To drop a system versioned table you first have to turn off system versioning.

    CREATE TABLE [temporal]([id]        int IDENTITY(1, 1)
                                PRIMARY KEY CLUSTERED,
                       [ValidFrom] datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN
                                        NOT NULL,
                    [ValidTo]   datetime2(7) GENERATED ALWAYS AS ROW END HIDDEN
                                        NOT NULL,
                    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])) WITH(SYSTEM_VERSIONING = ON(HISTORY_TABLE = [dbo].[Temporal_History]));


drop table temporal;

this will result in

Drop table operation failed on table 'tempdb.dbo.temporal' because it is not a supported operation on system-versioned temporal tables.

if you turn off system version you will be able to drop table. dont forget to drop the history table as well if you no longer need it

ALTER TABLE dbo.temporal  SET ( SYSTEM_VERSIONING = Off )

drop table temporal;
drop table Temporal_History

Answer 2

As mentioned, you need to turn SYSTEM_VERSIONING off. You should also drop the history table.

To do this in an automated fashion in a script, use the following

DECLARE @schema sysname = 'dbo';
DECLARE @table sysname = 'YourTable';

DECLARE @historyTable nvarchar(500) = (
    SELECT QUOTENAME(sHist.name) + '.' + QUOTENAME(tHist.name)
    FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    JOIN sys.tables tHist ON tHist.object_id = t.history_table_id
    JOIN sys.schemas sHist ON sHist.schema_id = tHist.schema_id
    WHERE s.name = @schema
      AND t.name = @table
);

DECLARE @sql nvarchar(max);

IF @historyTable IS NOT NULL
    SET @sql = '
ALTER TABLE ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
  SET ( SYSTEM_VERSIONING = OFF );

DROP TABLE ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ';
DROP TABLE ' + @historyTable + ';';

ELSE
    SET @sql = 'DROP TABLE ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ';';

EXEC (@sql);

Answer 3

This also could be wrapped up in a sp:

BEGIN TRY
    BEGIN TRAN;

    DECLARE @TableName VARCHAR(200),
            @SchemaName VARCHAR(50) = 'dbo',
            @HistoryTableName VARCHAR(200),
            @HistoryTableSchema VARCHAR(50) = 'dbo',
            @DropFkScript NVARCHAR(MAX) = N'',
            @DisableVersioningScript NVARCHAR(MAX) = N'',
            @DropTemporaryTableScript NVARCHAR(MAX) = N'',
            @DropTableSql NVARCHAR(MAX) = N'';

    SET @TableName = 'yourtable';
    SET @SchemaName = 'yourschema';

    --check if the table exists
    IF EXISTS
    (
        SELECT 1
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = @SchemaName
              AND TABLE_NAME = @TableName
    )
    BEGIN
        --check if the table has fk's
        IF EXISTS
        (
            SELECT 1
            FROM sys.foreign_keys
            WHERE referenced_object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
        )
        BEGIN
            SELECT @DropFkScript
                = STRING_AGG(
                                'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].['
                                + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']',
                                ';'
                            )
            FROM sys.foreign_keys
            WHERE referenced_object_id = OBJECT_ID(@SchemaName + '.' + @TableName);
            --drop fk's
            EXEC sp_executesql @stmt = @DropFkScript;

        END;

        --check if the table is versioned
        IF EXISTS
        (
            SELECT 1
            FROM sys.tables p
                INNER JOIN sys.tables h
                    ON p.history_table_id = h.object_id
            WHERE p.[name] = @TableName
                  AND p.schema_id = SCHEMA_ID(@SchemaName)
                  AND p.temporal_type_desc = 'SYSTEM_VERSIONED_TEMPORAL_TABLE'
        )
        BEGIN
            SELECT @HistoryTableName = h.[name],
                   @HistoryTableSchema = s.[name]
            FROM sys.tables t
                JOIN sys.tables h
                    ON t.history_table_id = h.object_id
                JOIN sys.schemas s
                    ON h.schema_id = s.schema_id
            WHERE t.temporal_type = 2
                  AND SCHEMA_NAME(t.schema_id) = @SchemaName
                  AND t.[name] = @TableName;

            SET @DisableVersioningScript
                = N'ALTER TABLE ' + @SchemaName + N'.' + @TableName + N' SET ( SYSTEM_VERSIONING = OFF);';
            SET @DropTemporaryTableScript = N'DROP TABLE ' + @HistoryTableSchema + N'.' + @HistoryTableName + N';';

            --drop history table
            EXEC sp_executesql @stmt = @DisableVersioningScript;
            EXEC sp_executesql @stmt = @DropTemporaryTableScript;
        END;

        --drop main table
        SET @DropTableSql = N'DROP TABLE ' + @SchemaName + N'.' + @TableName + N';';
        EXEC sp_executesql @stmt = @DropTableSql;
    END;

    COMMIT TRAN;

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE();
    ROLLBACK TRANSACTION;
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;