4

Consider the following TSql code:

CREATE TABLE Employee 
(  
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED 
  , [Name] nvarchar(100) NOT NULL
  , [Position] varchar(100) NOT NULL 
  , [Department] varchar(100) NOT NULL
  , [Address] nvarchar(1024) NOT NULL
  , [AnnualSalary] decimal (10,2) NOT NULL
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )  
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

... and the following C# code using DACFx:

TSqlObject table;
TSqlObject nameColumn;

// picture some code instantiating the two variables above

var primaryKey = table.GetChildren().Single(x => x.ObjectType == ModelSchema.PrimaryKeyConstraint);
var isMax = nameColumn.Object.GetProperty<bool?>(Column.IsMax);

So here we see that I've queried the DACFx API to find out if the column instance is a MAX-kind of column, and to get information on the table's primary key.

Now I would like to know about the temporal setup of the table. I need to know if SYSTEM_VERSIONING is on, and if it is, what is the table that serves as the history table. I also would like to know which fields are used as the row start and end.

How can I know this using DACFx API?

Crono
  • 10,211
  • 6
  • 43
  • 75

1 Answers1

2

I know this an old question, but I just spent a whole day and finally figured out how can you pull the the SYSTEM_VERSIONING settings. Hopefully this will be helpful for someone else:

TSqlObject table;

// Code initializing the variable

// Get the type of retention. This can be either -1 (if Infinite)
// or one of the value in the TemporalRetentionPeriodUnit enum
var retentionUnit = table.GetProperty<int>(Table.RetentionUnit);

// Get the retention value. If retention is Infinite this will be -1
var retentionValue = table.GetProperty<int>(Table.RetentionValue);

// Get a reference to the history table. 
var historyTable = table.GetReferenced(Table.TemporalSystemVersioningHistoryTable);
Peter Staev
  • 1,119
  • 7
  • 12
  • How do you convert the int into an actual unit ie TemporalRetentionPeriodUnit Days,Weeks etc – Ewan Mar 01 '21 at 10:07
  • I didn't need to convert it in my case. But you can try with a table and see what each number corresponds to by changing the retention and checking what will the code return for the table. – Peter Staev Mar 11 '21 at 23:12
  • in the end i had to just copy the values out of one of the microsoft docs and hard code them – Ewan Mar 12 '21 at 08:40