You can always use the object explore panel of SSMS to check if a specific system versioned temporal table exists in the database or not. However, there are several situations where you need a script to find if the temporal table exists. For example, if you want to write a script to delete tables, then you need to check if a table is temporal and perform the deletion process in a different way than deleting normal table. Let us see how to check if a specific table is temporal or not using SQL script.
The easiest way to check if temporal table exists is by using the system table sys.tables. From sys.tables, you can use the columns temporal_type or temporal_type_desc for this purpose. temporal_type will return 0 for NON_TEMPORAL_TABLE, 1 for HISTORY_TABLE and 2 for SYSTEM_VERSIONED_TEMPORAL_TABLE. So, by checking whether temporal_type = 2 you will know whether the specific table is temporal or not.
Let’s see a sample code for check if table People is temporal or not:
IF EXISTS (
SELECT 1
FROM sys.tables
WHERE name = 'People'
AND temporal_type = 2
)
BEGIN
SELECT 'Yes'
END
GO
You can use the above example to check if the table is temporal and execute different set of SQL statements.
Related article
- Read more about system versioned temporal tables.
- Finding the history table of the system versioned temporal table using script.
Reference
- Read more about sys.tables at Microsoft Docs.