SQL Server 2016 Temporal tables metadata

Question

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

Answer 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

Answer 2

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

• temporal_type
• temporal_type_desc
• history_table_id