How to Disable and Drop all Temporal Tables from a database

Question

I'm working on a task where I need to backup and restore a database in another instance.

But, when I restore that database, I need to Drop all the History Tables associated with Temporal Tables.

Is there a quick way to do this? Any help is appreciated.

Answer 1

I believe that's what you need.

SET NOCOUNT ON ;
DECLARE @cmd NVARCHAR (MAX);

DECLARE
    @SchemaName VARCHAR(100),
    @TableName VARCHAR(100),
    @HistorySchemaName VARCHAR(100),
    @HistoryTableName VARCHAR(100);

SET @cmd = '' ;

SELECT TOP ( 1 )
       @SchemaName = SCHEMA_NAME(T1.schema_id ),
       @TableName = T1 .name,
       @HistorySchemaName = SCHEMA_NAME(T2.schema_id ),
       @HistoryTableName = T2 .name
FROM sys .tables T1
LEFT JOIN sys .tables T2
    ON T1.history_table_id = T2 .object_id
WHERE T1. temporal_type = 2
ORDER BY T1. name;


WHILE @@ROWCOUNT = 1
    BEGIN
        SET @cmd = 'ALTER TABLE ' + QUOTENAME (@SchemaName ) + '. ' + QUOTENAME(@TableName) + ' SET ( SYSTEM_VERSIONING = OFF );
DROP TABLE '       + QUOTENAME (@HistorySchemaName ) + '.' + QUOTENAME(@HistoryTableName );
        EXEC sp_executesql @cmd

        SELECT TOP ( 1 )
               @SchemaName = SCHEMA_NAME(T1.schema_id ),
               @TableName = T1 .name,
               @HistorySchemaName = SCHEMA_NAME(T2.schema_id ),
               @HistoryTableName = T2 .name
        FROM sys.tables T1
        LEFT JOIN sys.tables T2
            ON T1 .history_table_id = T2 .object_id
        WHERE
            T1 .temporal_type = 2
            AND T1 .name > @TableName
        ORDER BY T1 .name;
    END;