How to find the history table of a temporal table?

Even though, you can use the object explore panel of SSMS to find the history table attached to a system versioned temporal table, there are situations where you need a SQL script to find the same.

To find the name of the history table you can use the system table sys.tables along with the system function OBJECT_NAME. From sys.tables, you have to use the column history_table_id to get the object_id of the history table for the given current table. This history_table_id will return object id only when the given table is current / temporal table, else it will return NULL.

For example, consider the table People is a temporal table (current table). Here is the script to get the name of the corresponding history table. Here is the script.

SELECT 
	OBJECT_NAME(history_table_id) AS 'History Table'
FROM sys.tables
WHERE name = 'People'
GO

Related Article

• Simple SQL statement to check if system versioned temporal table exists.

Reference

• Read more about sys.tables at Microsoft Docs.