List temporal tables in Teradata database

Temporal tables in Teradata database allows us to query table like it would be in specific past time.

Query below returns all temporal tables.


SELECT  DatabaseName,
        CASE TemporalProperty
            WHEN 'S' THEN 'System-versioned system-time table'
            WHEN 'U' THEN 'Bitemporal table'
            WHEN 'X' THEN 'Temporal table not system-versioned'
            END AS TemporalType
FROM    DBC.TablesV
WHERE   DatabaseName NOT IN ('All', 'Crashdumps', 'DBC', 'dbcmngr',
        'Default', 'External_AP', 'EXTUSER', 'LockLogShredder', 'PUBLIC',
        'Sys_Calendar', 'SysAdmin', 'SYSBAR', 'SYSJDBC', 'SYSLIB', 
        'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
        'TDStats', 'tdwm', 'SQLJ', 'SYSSPATIAL')
AND TemporalProperty IN ('S', 'U', 'X')
ORDER BY    DatabaseName,


  • DatabaseName - name of the period
  • TableName - temporal table schema name
  • TemporalType - type of temporal table.
    • System-versioned system-time table
    • Bitemporal table
    • Temporal table not system-versioned


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

Sample results

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.