List temporal tables in Azure SQL database

  • 1st April, 2019
Article for: SQL Server IBM Db2 Teradata

system-versioned temporal tables is change tracking managed by server itself. Server manages 2 separate tables - system-versioned temporal table with actual data and history table that stores change history.

Query below returns temporal tables paired with their history tables and retention period (how long history is stored).


select schema_name(t.schema_id) as temporal_table_schema, as temporal_table_name,
    schema_name(h.schema_id) as history_table_schema, as history_table_name,
    case when t.history_retention_period = -1 
        then 'INFINITE' 
        else cast(t.history_retention_period as varchar) + ' ' + 
            t.history_retention_period_unit_desc + 'S'
    end as retention_period
from sys.tables t
    left outer join sys.tables h
        on t.history_table_id = h.object_id
where t.temporal_type = 2
order by temporal_table_schema, temporal_table_name


  • temporal_table_schema - temporal table schema name
  • temporal_table_name - temporal table name
  • history_table_schema - history table schema name
  • history_table_name - history table name
  • retention_period - retention period - how long history is preserved (defined by DBA). Example values: INFINITE, 6 MONTHS, 30 DAYS


  • One row represents one temporal table
  • Scope of rows: only temporal tables defined in a database
  • Ordered by temporal table schema and table name

Sample results

Query returns 3 tables:

And this is how it looks in SSMS:

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.