Como você descarta uma tabela temporal no SQL Server?

Para eliminar uma tabela com versão do sistema, primeiro você precisa desativar o controle de versão do sistema.

    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;

isso resultará em

A operação de descarte de tabela falhou na tabela 'tempdb.dbo.temporal' porque não é uma operação com suporte em tabelas temporais com versão do sistema.

se você desativar a versão do sistema, poderá descartar a tabela. não se esqueça de descartar a tabela de histórico também se você não precisar mais dela

ALTER TABLE dbo.temporal  SET ( SYSTEM_VERSIONING = Off )

drop table temporal;
drop table Temporal_History

Como mencionado, você precisa SYSTEM_VERSIONINGdesligar. Você também deve descartar a tabela de histórico.

Para fazer isso de forma automatizada em um script, use o seguinte

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);

Isso também pode ser embrulhado em um 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;