Sql-server – How to check if the SYSTEM_VERSIONING for a table is ON

data-versioningddlsql serversql-server-2016

I know that SQL Server 2016 let us to use the SYSTEM_VERSIONING like :

CREATE TABLE EmpSalary  
(    
     EmpID int NOT NULL PRIMARY KEY CLUSTERED  
   , SalaryAmt decimal (10,2) NULL  
   , SalaryBeginDT datetime2 GENERATED ALWAYS AS ROW START NOT NULL  
   , SalaryEndDT datetime2 GENERATED ALWAYS AS ROW END NOT NULL  
   , PERIOD FOR SYSTEM_TIME (SalaryBeginDT, SalaryEndDT)     
)    
WITH (SYSTEM_VERSIONING = ON);

Also to deactivate this, it is only necessary to ALTER the table:

ALTER TABLE EmpSalary SET (SYSTEM_VERSIONING = OFF );

My question is how to check if the SYSTEM_VERSIONING for a table is ON and then ALTER the table?

Best Answer

You could query sys.tables

ALTER TABLE EmpSalary SET (SYSTEM_VERSIONING = On );
select name, temporal_type, temporal_type_desc from sys.tables where name = 'EmpSalary'

| name      | temporal_type | temporal_type_desc              |
|-----------|---------------|---------------------------------|
| EmpSalary | 2             | SYSTEM_VERSIONED_TEMPORAL_TABLE |

ALTER TABLE EmpSalary SET (SYSTEM_VERSIONING = OFF );
select name, temporal_type, temporal_type_desc from sys.tables where name = 'EmpSalary'

| name      | temporal_type | temporal_type_desc |
|-----------|---------------|--------------------|
| EmpSalary | 0             | NON_TEMPORAL_TABLE |