System versioning (temporal tables) on all tables in DB

Question

Is there a way to create System Versioning for all existing tables in one query in SQL-Server? Instead of doing one table/entity at a time. I have this and it works, just one table at a time..

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

Why do you want to do that? What is the *actual* problem you want to solve? Temporal tables aren't a quick&dirty auditing or change tracking solution. It's a feature only suitable for *some* problems with its own advantages and disadvantages. It adds quite a bit of complexity by adding extra tables and columns, taking up a lot more storage — Panagiotis Kanavos, Jul 31 '18 at 08:09
Its supposded to be all system versioned in a system Im working with, due to automated write-over on rows when the system gathers data. I want to solve it by doing it in one go instead of doing one by one. — CobraKaj, Jul 31 '18 at 08:24
That doesn't answer my comment. That's not a problem description, it's the attempted solution. Why make everything a temporal table? What is the problem you are trying to solve? There are probably easier, faster, safer ways to do it. Change tracking, auditing, point in time restores, partitioning — Panagiotis Kanavos, Jul 31 '18 at 08:28
As for this question, there are a lot of duplicate questions that show how to retrieve all table names from `sys.tables`, use it to construct a query string and execute it with `sp_executesql`. *Creating* the history tables will be harder, you'll have to reconstruct the entire table creation query. Check [this similar question](https://stackoverflow.com/questions/706664/generate-sql-create-scripts-for-existing-tables-with-query) — Panagiotis Kanavos, Jul 31 '18 at 08:30
I dont know what else to tell you than this is what have been requested and they always do this.. The question is if its possible to do on all entities within a DB. Is there an answer to that? I forgot to mention that it should be on already existing tables. — CobraKaj, Jul 31 '18 at 08:31
Check [this](https://stackoverflow.com/questions/155246/how-do-you-truncate-all-tables-in-a-database-using-tsql). It uses the undocumented `sp_MSForEachTable` to run a script for each user table in the database. You'll have to create an appropriate script, possibly the same one you have, replacing the `table` name with `?`. [This question](https://stackoverflow.com/questions/4499155/script-to-delete-all-non-system-objects-in-sql-server-2008) shows how to *generate* the scripts using SELECT statements. — Panagiotis Kanavos, Jul 31 '18 at 08:37

SqlKindaGuy · Accepted Answer · 2018-07-31T11:10:07.027

You can do it like this:

NB: The 0 in a.temporal_type will make sure you only loop through tables that has not any version on it.

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

@CobraKaj Okay, if it works then please mark it as correct so other can benefit from it — SqlKindaGuy, Jul 31 '18 at 20:59
I actually went by with a different method. But I'll upvote your reply as it looks good and can help others — CobraKaj, Aug 09 '18 at 09:19

System versioning (temporal tables) on all tables in DB

1 Answers1