Helpex - Trao đổi & giúp đỡ Đăng nhập
8

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"

8 hữu ích 0 bình luận 4.0k xem chia sẻ
13
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

13 hữu ích 0 bình luận chia sẻ
3

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

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 hữu ích 0 bình luận chia sẻ
1

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';
1 hữu ích 0 bình luận chia sẻ
0

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

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
0 hữu ích 1 bình luận chia sẻ
loading
Không tìm thấy câu trả lời bạn tìm kiếm? Duyệt qua các câu hỏi được gắn thẻ sql sql server sql-server-2016 , hoặc hỏi câu hỏi của bạn.

Có thể bạn quan tâm

loading