如何在 SQL Server 中删除时态表？

要删除系统版本控制表，您首先必须关闭系统版本控制。

    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;

这将导致

表“tempdb.dbo.temporal”上的删除表操作失败，因为它不是系统版本化时态表上受支持的操作。

如果您关闭系统版本，您将能够删除表。如果您不再需要它，也不要忘记删除历史记录表

ALTER TABLE dbo.temporal  SET ( SYSTEM_VERSIONING = Off )

drop table temporal;
drop table Temporal_History

如前所述，您需要SYSTEM_VERSIONING关闭。您还应该删除历史记录表。

要在脚本中以自动方式执行此操作，请使用以下命令

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

这也可以包含在 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;