Get the list of temporal tables in SQL Server DB

In my earlier articles, we have seen how to check if a given table is temporal or not and to get the history table of a temporal table using the system view sys.tables. Using similar technique, here we will see how to get the list of temporal tables and their corresponding history tables in a database.

In sys.tables there are specific columns (temporal_type and temporal_type_desc and history_table_id) to identify whether a table is system-versioned temporal table or history table. Using these columns, you can easily get the list of all the temporal current tables and the history tables and get the list of such tables.

Let us see an example script to get the list of all the system-versioned temporal current table along with its history table in the sample WideWorldImporters database.

SELECT 
	OBJECT_NAME(object_id) AS 'Temporal Table'
	,OBJECT_NAME(history_table_id) AS 'History Table'
FROM sys.tables
WHERE temporal_type = 2
GO

Reference

• More about temporal tables at Microsoft Docs.