如何判断sql表是否是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

使用SQL Server 2016支持时态表我想知道是否有办法确定表当前是否属于临时表？就像是

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

select * from sys.objects where object_id（'dbo.MyTable'，'u'）= parent_object_id and type_desc =“SYSTEM_VERSIONED”

4 个解决方案

#1

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

0 = NON_TEMPORAL_TABLE

1 = HISTORY_TABLE

1 = HISTORY_TABLE

2 = SYSTEM_VERSIONED_TEMPORAL_TABLE

2 = SYSTEM_VERSIONED_TEMPORAL_TABLE

Documentation

文档

#2

Another way of listing temporal tables with their history tables together is given in this SQL tutorial as List Temporal and History Tables in a SQL Server Database

将时态表与历史表一起列出的另一种方法在本SQL教程中给出，如SQL Server数据库中的列表时间表和历史表

select
 t.object_id,
 t.name,
 t.temporal_type,
 t.temporal_type_desc,
 h.object_id,
 h.name,
 h.temporal_type,
 h.temporal_type_desc
from sys.tables t
inner join sys.tables h on t.history_table_id = h.object_id

#3

Here is a simple answer to the original basic question:

这是对原始基本问题的简单回答：

SELECT *
FROM sys.tables
WHERE name = 'MyTable'
    AND schema_id = SCHEMA_ID('dbo')
    AND temporal_type_desc = 'SYSTEM_VERSIONED_TEMPORAL_TABLE'

And here is a similar query looking for the actual system managed history table:

这是一个类似的查询，查找实际的系统管理历史表：

SELECT h.* FROM sys.tables p 
INNER JOIN sys.tables h
    ON p.history_table_id = h.object_id
WHERE p.name = 'MyTable'
    AND p.schema_id = SCHEMA_ID('dbo')
    AND p.temporal_type_desc = 'SYSTEM_VERSIONED_TEMPORAL_TABLE';

#4

This query will give you the system versioned tables, the associated history tables, the retention policy, and whether the retention policy is enabled at the database level.

此查询将为您提供系统版本化表，关联的历史记录表，保留策略以及是否在数据库级别启用了保留策略。

From Microsoft Docs

来自Microsoft Docs

SELECT DB.is_temporal_history_retention_enabled,
SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
T1.name as TemporalTableName,  SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
T2.name as HistoryTableName,T1.history_retention_period,
T1.history_retention_period_unit_desc
FROM sys.tables T1  
OUTER APPLY (select is_temporal_history_retention_enabled from sys.databases
where name = DB_NAME()) AS DB
LEFT JOIN sys.tables T2   
ON T1.history_table_id = T2.object_id WHERE T1.temporal_type = 2

#1