如何识别Teradata数据库中的时态表？查询时态表及类型标识方法

How to Identify Temporal Tables in Teradata? #

Teradata supports three core types of temporal tables: Transactional Temporal Tables, Period Temporal Tables, and Bi-temporal Tables (a mix of the first two). You can spot them in two straightforward ways:

• Inspect the table definition: Run a SHOW TABLE command for the target table. If it’s temporal, you’ll see syntax that reveals its type:

  • Transactional tables include a FOR SYSTEM_TIME clause paired with start/end timestamp columns.
  • Period tables have a FOR BUSINESS_TIME clause referencing a PERIOD-type column.
  • Bi-temporal tables will include both clauses.

• Query system catalog views: Teradata maintains dedicated system views to track all temporal tables—this is the most efficient way to identify them at scale, which we’ll dive into next.

SQL Query to Fetch All Temporal Tables & Dedicated Identification Columns #

Absolutely! Teradata’s system catalog has built-in views that let you pull a full list of temporal tables and their types directly. Here’s how to do it:

1. List All Temporal Tables with Their Types #

Use the DBC.TemporalTables view—it tracks every temporal table in the system. The TemporalType column explicitly identifies the table’s category:

• 1 = Transactional Temporal
• 2 = Period Temporal
• 3 = Bi-temporal

SELECT 
  DatabaseName AS Database_Name,
  TableName AS Table_Name,
  CASE TemporalType 
    WHEN 1 THEN 'Transactional Temporal'
    WHEN 2 THEN 'Period Temporal'
    WHEN 3 THEN 'Bi-temporal'
  END AS Temporal_Table_Type
FROM DBC.TemporalTables
ORDER BY Database_Name, Table_Name;

2. View Specific Temporal Columns for Each Table #

To see which columns drive the temporal behavior, join DBC.TemporalTables with DBC.TemporalTableColumns. This view breaks down each column’s role:

• ColumnType 1 = System Start Time (for transactional tables)
• ColumnType 2 = System End Time (for transactional tables)
• ColumnType 3 = Business Period Column (for period tables)

SELECT 
  tt.DatabaseName AS Database_Name,
  tt.TableName AS Table_Name,
  CASE tt.TemporalType 
    WHEN 1 THEN 'Transactional Temporal'
    WHEN 2 THEN 'Period Temporal'
    WHEN 3 THEN 'Bi-temporal'
  END AS Temporal_Table_Type,
  ttc.ColumnName AS Temporal_Column_Name,
  CASE ttc.ColumnType 
    WHEN 1 THEN 'System Start Timestamp'
    WHEN 2 THEN 'System End Timestamp'
    WHEN 3 THEN 'Business Period Column'
  END AS Column_Role
FROM DBC.TemporalTables tt
INNER JOIN DBC.TemporalTableColumns ttc
  ON tt.DatabaseName = ttc.DatabaseName 
  AND tt.TableName = ttc.TableName
ORDER BY tt.DatabaseName, tt.TableName, ttc.ColumnType;

Do Temporal Tables Have Dedicated Columns for Identification? #

Yes! Each temporal table type relies on dedicated columns (either system-generated or user-defined):

• Transactional Temporal Tables: Include two timestamp columns (default names SysStartTime and SysEndTime, customizable) that track when rows were active in the system.
• Period Temporal Tables: Require a PERIOD-type column (e.g., ValidTime) that defines the business-relevant active period for rows.
• Bi-temporal Tables: Combine both the system timestamp columns and the business period column.

These columns are explicitly tracked in the system catalog views above, making it easy to map which columns enable the table’s temporal functionality.

内容的提问来源于stack exchange，提问作者PseudoAccount