How to get a list of tables and their system-versioned child tables

Tom van Stiphout 1,701 Reputation points MVP

select OBJECT_NAME(object_id) as theTable, * from sys.tables
where temporal_type = 1;
This gives me the history tables. But not their parents. parent_object_id is 0, which I call a real deficiency.

So how do I get the parent tables and their history tables?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,078 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,579 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Viorel 113.8K Reputation points

    Check this query:

    select as MainTable, as ChildHistoryTable
    from sys.tables t
    inner join sys.tables h on h.object_id = t.history_table_id
    where t.temporal_type = 2 and h.temporal_type = 1
    1 person found this answer helpful.
    0 comments No comments