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

Tom van Stiphout 1,701 Reputation points MVP
2022-04-08T03:56:48.033+00:00

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
Transact-SQL
Transact-SQL
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
    2022-04-08T07:43:17.607+00:00

    Check this query:

    select t.name as MainTable, h.name 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