Temporal tables can be a powerful way to track historical data changes over time in our SQL Server databases. When we’re working with a database that uses temporal tables, we may need to identify all such tables quickly. Or we may simply want to check to see if it has any temporal tables.

Either way, the following SQL queries can be used to return a list of all temporal tables in the database.

Simple Query

Here’s a simple query that lists the names of all current tables and their respective history table in the database:

SELECT 
    t.name AS TemporalTableName,
    th.name AS HistoryTableName
FROM sys.tables t
INNER JOIN sys.tables th ON t.history_table_id = th.object_id
WHERE t.temporal_type = 2
ORDER BY t.name;

Example result:

TemporalTableName  HistoryTableName                   
-----------------  -----------------------------------
EmployeeRecords    MSSQL_TemporalHistoryFor_1026102696
OrderHistory       MSSQL_TemporalHistoryFor_978102525 
ProductInventory   ProductInventoryHistory            

A More Detailed Query

Here’s a query that provides more information about each temporary table returned:

SELECT 
    s.name AS SchemaName,
    t.name AS TemporalTableName,
    th.name AS HistoryTableName,
    t.create_date AS TableCreationDate,
    t.modify_date AS TableLastModifiedDate,
    p.rows AS ApproximateRowCount,
    CASE 
        WHEN t.temporal_type = 2 THEN 'System-versioned'
        ELSE 'Not temporal'
    END AS TemporalType,
    OBJECT_NAME(t.history_table_id) AS HistoryTableName,
    t.is_memory_optimized AS IsMemoryOptimized,
    t.durability_desc AS Durability,
    i.name AS ClusteredIndexName,
    i.type_desc AS ClusteredIndexType
FROM sys.tables t
LEFT JOIN sys.tables th ON t.history_table_id = th.object_id
LEFT JOIN sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN sys.indexes i ON t.object_id = i.object_id AND i.type IN (1, 5) -- Clustered and Clustered Columnstore indexes
LEFT JOIN sys.partitions p ON t.object_id = p.object_id AND i.index_id = p.index_id
WHERE t.temporal_type = 2
ORDER BY s.name, t.name;

Example result:

SchemaName  TemporalTableName  HistoryTableName                                                         TableCreationDate         TableLastModifiedDate     ApproximateRowCount  TemporalType      IsMemoryOptimized  Durability       ClusteredIndexName              ClusteredIndexType
----------  -----------------  -----------------------------------------------------------------------  ------------------------  ------------------------  -------------------  ----------------  -----------------  ---------------  ------------------------------  ------------------
dbo         EmployeeRecords    MSSQL_TemporalHistoryFor_1026102696,MSSQL_TemporalHistoryFor_1026102696  2024-07-27T12:20:47.226Z  2024-07-27T12:22:24.960Z  15                   System-versioned  false              SCHEMA_AND_DATA  PK__Employee__7AD04FF11E6776B1  CLUSTERED         
dbo         OrderHistory       MSSQL_TemporalHistoryFor_978102525,MSSQL_TemporalHistoryFor_978102525    2024-07-27T12:20:15.600Z  2024-07-27T12:20:15.600Z  12                   System-versioned  false              SCHEMA_AND_DATA  PK__OrderHis__C3905BAFF9EFBBA0  CLUSTERED         
dbo         ProductInventory   ProductInventoryHistory,ProductInventoryHistory                          2024-07-27T03:56:24.156Z  2024-07-27T04:43:53.886Z  3                    System-versioned  false              SCHEMA_AND_DATA  PK__ProductI__B40CC6ED09B403B1  CLUSTERED         

3 row(s) returned

This query returns the following columns:

1. SchemaName: The schema of the temporal table
2. TemporalTableName: The name of the temporal table
3. HistoryTableName: The name of the associated history table
4. TableCreationDate: When the table was created
5. TableLastModifiedDate: When the table was last modified
6. ApproximateRowCount: An estimate of the number of rows in the table
7. TemporalType: Confirms that the table is system-versioned
8. IsMemoryOptimized: Whether the table is memory-optimized
9. Durability: The durability setting of the table
10. ClusteredIndexName: The name of the clustered index on the table
11. ClusteredIndexType: The type of the clustered index

The above scripts should work on SQL Server 2016 and later (temporal tables were introduced in SQL Server 2016).