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

Question

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?

Answer 1

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