1

Is there any way to get metadata (information scheme) information about temporal table feature for the table, like:

  • Is DATA_CONSISTENCY_CHECK TURNED ON OR OFF
  • What name of history table
  • Is SYSTEM_VERSIONING turned for the table
1

Is DATA_CONSISTENCY_CHECK TURNED ON OR OFF?

This is a one time operation when the table is created. This will not appear anywhere in the database because it is a one time operation.

When creating a link to an existing history table, you can choose to perform a data consistency check. This data consistency check ensures that existing records do not overlap. Performing the data consistency check is the default. Use this argument in conjunction with the PERIOD FOR SYSTEM_TIME and GENERATED ALWAYS AS ROW { START | END } arguments to enable system versioning on a table.

You can see this yourself:

select * from sys.tables
order by name

select * from sys.key_constraints

select * from sys.check_constraints

select * from sys.sysconstraints

select definition,name
from sys.check_constraints

select definition,name
from sys.default_constraints

Likewise, you can perform this check after the creation of the table too:

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

MSDN on DBCC CHECKCONSTRAINTS

MSDN Source

What is the name of history table?

You can use this query to find the history tables:

SELECT NAME
    ,object_id
    ,temporal_type
    ,temporal_type_desc
FROM sys.tables
WHERE temporal_type_desc = 'History_Table'
ORDER BY NAME

If you query around sys.tables, you will find a lot of handy information.

Source: MS SQL Tips by Ameena Lalani

Is SYSTEM_VERSIONING turned on for the table?

SELECT name, object_id,temporal_type,temporal_type_desc FROM sys.tables
where temporal_type in (1,2)
order by name
0
0

You can get the versioning information from sys.tables, specifically:

  • temporal_type
  • temporal_type_desc
  • history_table_id
4
  • Cool! Thanks! And what about DATA_CONSISTENCY_CHECK option? – Kyrylo Bezpalyi Mar 27 '18 at 14:25
  • Well, it's done by default--are you trying to link to an OLD history table? – scsimon Mar 27 '18 at 14:32
  • I need to remove old data from history and to do this, as you know, I need to turn off and turn on again the SYSTEM_VERSIONING state before and after the cleanup. That's why I want to extract old value to apply it when restore SYSTEM_VERSIONING state. – Kyrylo Bezpalyi Mar 27 '18 at 14:38
  • @KyryloBezpalyi , the setting DATA_CONSISTENCY_CHECK is set to ON by default when you create the temporal table. If you don't specify the command and set it to OFF, it will run automatically as ON. What this setting does is run a DBCC CHECKCONSTRAINTS specifically on that table and the SYSTEM_VERSION CONSTRAINT to verify the integrity of the data. Think of this as asking the database to take a one time action when creating the table. – Shaulinator Mar 27 '18 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.