SQL Server 2016 Temporal tables metadata 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
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