How to determine if sql table is temporary?

Question

With SQL Server 2016 supporting Temporary tables I wonder if there is a way to determine if a table is currently temporary? Sort of

select * from sys.objects where object_id('dbo.MyTable', 'u') = parent_object_id and type_desc = "SYSTEM_VERSIONED"

Answer 1

 SELECT temporal_type FROM sys.tables WHERE object_id = OBJECT_ID('dbo.MyTable', 'u') 

0 = NON_TEMPORAL_TABLE

1 = HISTORY_TABLE

2 = SYSTEM_VERSIONED_TEMPORAL_TABLE

Documentation

Answer 2

Another way to list temporary tables with their history tables together is provided in this SQL tutorial as Listing temporary and historical tables in a 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 

Answer 3

This query will provide you with system version tables, related history tables, retention policies, and whether retention policies will be enabled at the database level.

From Microsoft docs

 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 

Answer 4

Here is a simple answer to the original basic question:

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

And here is a similar query that searches for the actual history table of a managed system:

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