sqlsql-serversql-server-2016

How to determine if sql table is Temporal?

With SQL Server 2016 supporting Temporal Tables I wonder if there is a way to determine if a table is currently temporal? Something like

select * from sys.objects where object_id('dbo.MyTable', 'u') = parent_object_id and type_desc = "SYSTEM_VERSIONED"

Solution 
  • SELECT temporal_type
FROM   sys.tables
WHERE  object_id = OBJECT_ID('dbo.MyTable', 'u')

    0 = NON_TEMPORAL_TABLE

    1 = HISTORY_TABLE

    2 = SYSTEM_VERSIONED_TEMPORAL_TABLE

    Documentation