ftJz5MAU

1DROP PROCEDURE IF EXISTS sp_CleanupHistoryData;  
2GO  
3
4CREATE PROCEDURE sp_CleanupHistoryData  
5         @temporalTableSchema sysname  
6       , @temporalTableName sysname  
7       , @cleanupOlderThanDate datetime2  
8AS  
9    DECLARE @disableVersioningScript nvarchar(max) = '';  
10    DECLARE @deleteHistoryDataScript nvarchar(max) = '';  
11    DECLARE @enableVersioningScript nvarchar(max) = '';  
12
13DECLARE @historyTableName sysname    
14DECLARE @historyTableSchema sysname    
15DECLARE @periodColumnName sysname    
16
17/*Generate script to discover history table name and end of period column for given temporal table name*/  
18EXECUTE sp_executesql   
19    N'SELECT @hst_tbl_nm = t2.name, @hst_sch_nm = s.name, @period_col_nm = c.name  
20        FROM sys.tables t1   
21           JOIN sys.tables t2 on t1.history_table_id = t2.object_id  
22        JOIN sys.schemas s on t2.schema_id = s.schema_id  
23            JOIN sys.periods p on p.object_id = t1.object_id  
24           JOIN sys.columns c on p.end_column_id = c.column_id and c.object_id = t1.object_id  
25                  WHERE   
26                 t1.name = @tblName and s.name = @schName'  
27                , N'@tblName sysname  
28                , @schName sysname  
29                , @hst_tbl_nm sysname OUTPUT  
30                , @hst_sch_nm sysname OUTPUT  
31                , @period_col_nm sysname OUTPUT'  
32                , @tblName = @temporalTableName  
33                , @schName = @temporalTableSchema  
34                , @hst_tbl_nm = @historyTableName OUTPUT  
35                , @hst_sch_nm = @historyTableSchema OUTPUT  
36                , @period_col_nm = @periodColumnName OUTPUT   
37
38IF @historyTableName IS NULL OR @historyTableSchema IS NULL OR @periodColumnName IS NULL  
39    THROW 50010, 'History table cannot be found. Either specified table is not system-versioned temporal or you have provided incorrect argument values.', 1  
40
41/*Generate 3 statements that will run inside a transaction: SET SYSTEM_VERSIONING = OFF, DELETE FROM history_table, SET SYSTEM_VERSIONING = ON */  
42SET @disableVersioningScript =  @disableVersioningScript + 'ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + '] SET (SYSTEM_VERSIONING = OFF)'  
43SET @deleteHistoryDataScript =  @deleteHistoryDataScript + ' DELETE FROM  [' + @historyTableSchema + '].[' + @historyTableName + ']   
44     WHERE ['+ @periodColumnName + '] < ' + '''' + convert(varchar(128), @cleanupOlderThanDate, 126) +  ''''   
45SET @enableVersioningScript =  @enableVersioningScript + ' ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + ']   
46    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + '].[' + @historyTableName + '], DATA_CONSISTENCY_CHECK = OFF )); '   
47
48BEGIN TRAN  
49    EXEC (@disableVersioningScript);  
50    EXEC (@deleteHistoryDataScript);  
51    EXEC (@enableVersioningScript);  
52COMMIT;