Check for Temporal Table in SQL Server Database

select
	 t.object_id,
	 t.name,
	 t.temporal_type,
	 t.temporal_type_desc,
	 h.object_id,
	 h.name,
	 h.temporal_type,
	 h.temporal_type_desc
from 
	sys.tables t
	inner join sys.tables h on t.history_table_id = h.object_id 

SELECT DB.is_temporal_history_retention_enabled,
SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
T1.name as TemporalTableName,  SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
T2.name as HistoryTableName,T1.history_retention_period,
T1.history_retention_period_unit_desc
FROM sys.tables T1  
OUTER APPLY (select is_temporal_history_retention_enabled from sys.databases
where name = DB_NAME()) AS DB
LEFT JOIN sys.tables T2   
ON T1.history_table_id = T2.object_id WHERE T1.temporal_type = 2

---

SELECT 
	*
FROM 
	sys.tables
WHERE
	schema_id = SCHEMA_ID('dbo')
    AND 
	temporal_type_desc = 'SYSTEM_VERSIONED_TEMPORAL_TABLE'

SELECT 
	h.* 
FROM 
	sys.tables p 
	INNER JOIN sys.tables h ON p.history_table_id = h.object_id
WHERE 
    p.schema_id = SCHEMA_ID('dbo')
    AND 
	p.temporal_type_desc = 'SYSTEM_VERSIONED_TEMPORAL_TABLE';

Reference:

https://stackoverflow.com/questions/31124963/how-to-determine-if-sql-table-is-temporal