sql-server - 数据库中所有表的系统版本控制（临时表）

Question

有没有办法在 SQL-Server 的一个查询中为所有现有表创建系统版本控制？而不是一次做一个表/实体。我有这个，它可以工作，一次只有一张桌子..

Alter table schema.table
add
SysStartTime datetime2 generated always as row start not null default getutcdate(),
SysEndTime   datetime2 generated always as row end   not null default convert(datetime2, '9999-12-31 23:59:59.9999999'),
period for system_time (SysStartTime, SysEndTime);

alter table schema.table
set (system_versioning = on (HISTORY_TABLE = [schema].[table_history]));

标签： sql-server

Answer 1

你可以这样做：

注意： a.temporal_type 中的 0 将确保您仅循环遍历没有任何版本的表。

declare @SQLAddColumns nvarchar(max)
declare @SQLAddHistory nvarchar(max)
declare @SchemaName nvarchar(100)
declare @TableName nvarchar(100)


DECLARE mycursor CURSOR FOR

select b.name,a.name from sys.tables  a 
inner join sys.schemas b on a.schema_id = b.schema_id 
where a.temporal_type = 0 --Only tables without version


OPEN mycursor
FETCH NEXT  FROM mycursor into @SchemaName, @TableName 
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQLAddColumns = 'Alter table ['+@SchemaName+'].['+@TableName+']
add
SysStartTime datetime2 generated always as row start not null default getutcdate(),
SysEndTime   datetime2 generated always as row end   not null default convert(datetime2, ''9999-12-31 23:59:59.9999999''),
period for system_time (SysStartTime, SysEndTime);'

SET @SQLAddHistory = '
alter table ['+@SchemaName+'].['+@TableName+']
set (system_versioning = on (HISTORY_TABLE = ['+@SchemaName+'].['+@TableName+'_history]));'

print @SQLAddColumns
exec(@SQLAddColumns)

Print ''
Print 'AddHistory'
Print ''

print @SQLAddHistory
exec(@SQLAddHistory)
FETCH NEXT  FROM mycursor into @SchemaName, @TableName 

END
CLOSE mycursor
DEALLOCATE mycursor