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

Query below returns all temporal tables.

Query

SELECT  DatabaseName,
        TableName,
        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', 
        'SystemFe', 'SYSUDTLIB', 'SYSUIF', 'TD_SERVER_DB', 'TD_SYSFNLIB',
        'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
        'TDStats', 'tdwm', 'SQLJ', 'SYSSPATIAL')
AND TemporalProperty IN ('S', 'U', 'X')
ORDER BY    DatabaseName,
            TableName;

Columns

• 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

Rows

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

Sample results