TDM not scripting drops of temporal tables

Toad for SQL Server
1

I`m running 7.3.2.252 of Toad Data Modeller for SQL Server and when I get it generate the create script, it includes the drop statements for all the entities except for the temporal tables (which the script itself creates).

Is there a reason for this or am I missing something please?

2

Welcome to the community forum on ToadWorld for Toad for SQL Server.

Just making sure we're talking about temporal tables, and not temporary tables. TDM should be dropping any temporary tables it creates, but if not, might need to send this to Quest Support so they can document properly for other users, and get with the Dev team for a fix.

If we're talking about temporal (system versioned) tables, then I'm assuming that you (or someone) turned on the system versioning attribute for one or more entities in the data model, otherwise, I don't know of a case where TDM would create these on its own. Temporal tables require special handling to drop. These types of tables track data changes, so to drop them, check the SQL Server docs... you'll probably need to ALTER the underlying table to turn off versioning, for one thing, and then go about dropping, but I would check with the database documentation.

3

Thanks for your response Gary. Yes, I`m talking about temporal tables.

TDM now handles the creation of temporal tables and the scripting will output the correct SQL however it only issues the DROP commands to the table itself and not the temporal table behind it.

So for example the script output does the following:

DROP TABLE [dbo].[mytesttable]
GO

CREATE TABLE [dbo].[mytesttable]
(
 [Id] Int IDENTITY NOT NULL,
 [validFrom] Datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
 [validTo] Datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
 CONSTRAINT [PK_proactiveCases] PRIMARY KEY ([Id]),
 PERIOD FOR SYSTEM_TIME ( [validFrom], [validTo] )
)
WITH (SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[mytestable_history], DATA_CONSISTENCY_CHECK = ON ))
go

So it drops the main table but not the history table - so the script fails because it can`t re-create the history table as it already exists and I have to manually delete each orphaned history one by one.