Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link: http://hub-nl.insight.com/i/692679

Contents of this Issue

Navigation

Page 88 of 212

76 C H A P T E R 5 | Broader data access DECLARE @periodColumnName sysname -- Discover history table and end of period column for that table EXECUTE sp_executesql N'SELECT @hst_tbl_nm = t2.name, @hst_sch_nm = s.name, @period_col_nm = c.name FROM sys.tables t1 JOIN sys.tables t2 on t1.history_table_id = t2.object_id JOIN sys.schemas s on t2.schema_id = s.schema_id JOIN sys.periods p on p.object_id = t1.object_id JOIN sys.columns c on p.end_column_id = c.column_id and c.object_id = t1.object_id WHERE t1.name = @tblName and s.name = @schName', N'@tblName sysname, @schName sysname, @hst_tbl_nm sysname OUTPUT, @hst_sch_nm sysname OUTPUT, @period_col_nm sysname OUTPUT', @tblName = @temporalTableName, @schName = @temporalTableSchema, @hst_tbl_nm = @historyTableName OUTPUT, @hst_sch_nm = @historyTableSchema OUTPUT, @period_col_nm = @periodColumnName OUTPUT IF @historyTableName IS NULL OR @historyTableSchema IS NULL OR @periodColumnName IS NULL THROW 50010, 'History table cannot be found. Either specified table is not system-versioned temporal or you have provided incorrect argument values.', 1 SET @disableVersioningScript = @disableVersioningScript + 'ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + '] SET (SYSTEM_VERSIONING = OFF)' SET @deleteHistoryDataScript = @deleteHistoryDataScript + ' DELETE FROM [' + @historyTableSchema + '].[' + @historyTableName + '] WHERE [' + @periodColumnName + '] < ' + '''' + convert(varchar(128), @cleanupOlderThanDate, 126) + '''' SET @enableVersioningScript = @enableVersioningScript + ' ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + '] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + '].[' + @historyTableName + '], DATA_CONSISTENCY_CHECK = OFF )); ' BEGIN TRAN EXEC (@disableVersioningScript); EXEC (@deleteHistoryDataScript); EXEC (@enableVersioningScript); COMMIT; Reviewing temporal metadata Several metadata views and functions are available for accessing information about temporal tables. This information is useful for creating scripts to monitor the current state of your environment or to perform maintenance tasks. You can find metadata about tables, columns, and period columns in three system views, as shown in the following table: Metadata view Column Description sys.tables temporal_type Identifies the type of table: 0 = not temporal 1 = history table 2 = current table temporal_type_desc Provides a text description for the temporal type history_table_id Provides the object_id for the history table associated with a current table when applicable sys.columns generated_always_type Identifies the column type for period columns: 0 = not applicable

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016