Find if system versioned temporal table exists

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
Find if temporal table exists

You can use the above example to check if the table is temporal and execute different set of SQL statements.

Related article

Reference

Related Articles & Tools

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.