procedure to drop system-versioned temporal tables

Question

I'm looking for a procedure to drop system-versioned temporal tables, ideally without using dynamic SQL. I've looked through the Microsoft documentation and figured out how to get the autogenerated history table name, but I only know a little about cursors and even less about dynamic SQL.

You can't just drop a temporal table. You must first disable versioning, which will cause the history table to become an ordinary table. Then you can drop both the temporal table and its corresponding history table.

ALTER TABLE [dbo].[TemporalTest] SET ( SYSTEM_VERSIONING = OFF )
GO
DROP TABLE [dbo].[TemporalTest]
GO
DROP TABLE [dbo].[TemporalTestHistory]
GO

I am using temporal tables with autogenerated history tables, so I don't know their names. However, the Microsoft docs provide information about how to list the history tables, so I have a way of getting those names.

select schema_name(t.schema_id) as temporal_table_schema,
     t.name as temporal_table_name,
    schema_name(h.schema_id) as history_table_schema,
     h.name as history_table_name,
    case when t.history_retention_period = -1 
        then 'INFINITE' 
        else cast(t.history_retention_period as varchar) + ' ' + 
            t.history_retention_period_unit_desc + 'S'
    end as retention_period
from sys.tables t
    left outer join sys.tables h
        on t.history_table_id = h.object_id
where t.temporal_type = 2
order by temporal_table_schema, temporal_table_name

I was hoping I could use a subquery with the DROP statement, e.g. DROP TABLE (SELECT '#t'). This throws a syntax error.

I'm looking for a stored procedure which takes two parameters: the name of the table to be dropped and whether the drop should take place if there's any data in the table (eg, must ROWCOUNT=0).

Influential

Answer 1

You can't issue DDL against data (like the output of a subquery), or pass an entity name as a variable to a statement; you need dynamic SQL.

CREATE PROCEDURE dbo.DropTemporalTable
  @schema sysname = N'dbo',
  @table  sysname
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql nvarchar(max) = N'';

  SELECT @sql += N'ALTER TABLE ' + src + N' SET (SYSTEM_VERSIONING = OFF);
    DROP TABLE ' + src  + N';
    DROP TABLE ' + hist + N';'
  FROM
  (
    SELECT src = QUOTENAME(SCHEMA_NAME(t.schema_id))
               + N'.' + QUOTENAME(t.name),
          hist = QUOTENAME(SCHEMA_NAME(h.schema_id))
               + N'.' + QUOTENAME(h.name)
    FROM sys.tables AS t
    INNER JOIN sys.tables AS h
    ON t.history_table_id = h.[object_id]
    WHERE t.temporal_type = 2
      AND t.[schema_id] = SCHEMA_ID(@schema)
      AND t.name = @table
  ) AS x;

  EXEC sys.sp_executesql @sql;
END
GO

Poetess

This is awesome, thanks! I turned it into a procedure and parameterized it on @t. SELECT src = QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(t.name), hist = QUOTENAME(SCHEMA_NAME(h.schema_id)) + N'.' + QUOTENAME(h.name) FROM sys.tables AS t INNER JOIN sys.tables AS h ON t.history_table_id = h.[object_id] WHERE t.temporal_type = 2 AND t.name=@t – Influential

Answer 2

I am using temporal tables with autogenerated history tables, so I don't know their names. However, the Microsoft docs provide information about how to list the history tables, so I have a way of getting those names.

I don't know if you know this but you can specify the name of the history table in several ways.

If you create your own history table:

ALTER TABLE [TemporalTable] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[HistoryTable]))

Or if you want your history table created for you:

CREATE TABLE [dbo].[TemporalTable](
    [Id] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_TemporalTable_Id] DEFAULT(NEWID()),
    [SysStartTime] DATETIME2(2) GENERATED ALWAYS AS ROW START NOT NULL CONSTRAINT [DF_TemporalTable_SysStartTime] DEFAULT(SYSUTCDATETIME()),
    [SysEndTime] DATETIME2(2) GENERATED ALWAYS AS ROW END NOT NULL CONSTRAINT [DF_TemporalTable_SysEndTime] DEFAULT(CONVERT(DATETIME2(2),'9999-12-31 23:59:59.99')),
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[HistoryTable] )
)

Other than that I don't know of a way to create a stored procedure which takes a table name as a parameter and drops it without using dynamic SQL. Something like below will be necessary. Depending on if you want the stored procedure to look up the name of the history table linked to it you can add the query you mentioned in your post instead of a parameter.

CREATE PROCEDURE DeleteTemporalTable (@tableName NVARCHAR(MAX), @historyTableName) AS
BEGIN
    EXEC ('
    IF EXISTS (
        SELECT
            1
        FROM
            SYSOBJECTS
        WHERE
            id = OBJECT_ID(N''[dbo].[' + @tableName + ']'') AND
            OBJECTPROPERTY(id, N''IsTable'') = 1 AND
            OBJECTPROPERTY(id, N''TableTemporalType'') = 2
    )
    BEGIN
        ALTER TABLE [dbo].[' + @tableName + '] SET (SYSTEM_VERSIONING = OFF)
    END
    DROP TABLE IF EXISTS [dbo].[' + @tableName + ']
    DROP TABLE IF EXISTS [dbo].[' + @historyTableName + ']')
END

If you're just looking for a way to delete all tables, and you want something that works with temporal tables, this is a script I use:

DECLARE @temporalTableName varchar(max) = (SELECT TOP 1 [name] FROM sys.tables WHERE [temporal_type_desc] = 'SYSTEM_VERSIONED_TEMPORAL_TABLE')
WHILE @temporalTableName IS NOT NULL
BEGIN
    EXEC('ALTER TABLE [dbo].[' + @temporalTableName + '] SET (SYSTEM_VERSIONING = OFF)')
    SET @temporalTableName = (SELECT TOP 1 [name] FROM sys.tables WHERE [temporal_type_desc] = 'SYSTEM_VERSIONED_TEMPORAL_TABLE')
END
DECLARE @tableName varchar(max) = (SELECT TOP 1 [name] FROM sys.tables)
WHILE @tableName IS NOT NULL
BEGIN
    EXEC('DROP TABLE [dbo].[' + @tableName + ']')
    SET @tableName = (SELECT TOP 1 [name] FROM sys.tables)
END

Jackass

Very helpful insights, thanks! In this case I don't care about the name of the history table so prefer that it be autogenerated. As often happens with Microsoft products, I find myself surprised that they neglected some important basics. – Influential

@Influential The primary focus of T-SQL is to store and retrieve data, not to drop and create tables without knowing their names. – Poetess

I'm aware of the purpose of T-SQL. I'm also aware that Microsoft implemented temporal tables without extending DROP TABLE to play nicely, so I'm simply trying to script the ability to cleanly drop temporal tables with autogenerated history tables. – Influential

My point was that the primary use case of T-SQL is not dropping tables, regardless of the introduction of temporal. Same happens with schema-bound objects, foreign keys, etc. – Poetess

Answer 3

I've adapted Aaron's answer. Here's final solution:

CREATE OR ALTER PROCEDURE [dbo].[DropTemporalTable]
  @t as nvarchar(max),
  @s as nvarchar(max) = 'dbo'
-- See https://mcmap.net/q/1616425/-procedure-to-drop-system-versioned-temporal-tables/55524067#55524067
AS
BEGIN
-- TODO: check if src table is empty first
 DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'ALTER TABLE ' + src + N' SET (SYSTEM_VERSIONING = OFF);
DROP TABLE ' + src  + N';
DROP TABLE ' + hist + N';
'
FROM
(
  SELECT src = QUOTENAME(SCHEMA_NAME(t.schema_id))
             + N'.' + QUOTENAME(t.name),
        hist = QUOTENAME(SCHEMA_NAME(h.schema_id))
             + N'.' + QUOTENAME(h.name)
  FROM sys.tables AS t
  INNER JOIN sys.tables AS h
  ON t.history_table_id = h.[object_id]
  WHERE t.temporal_type = 2 AND t.name=@t AND t.schema_id=SCHEMA_ID(@s)
) AS x;

PRINT @sql;
--EXEC sys.sp_executesql @sql;
END

Influential

To be fair, your question says you are deriving a list of table names because you don't know them, and want to execute the drop for all the tables in that set. Making a stored procedure that takes a single table name as a parameter means (a) you have to know the table name first, and (b) now you have to write code that calls the stored procedure for every table name in your set. – Poetess

This also assumes that you won't ever have tables in any schema other than the caller's default schema. – Poetess

I've fixed the schema issue at least to a degree, making it an optional parameter that defaults to 'dbo'. Sorry if my prompt was unclear: I meant that I didn't know the name of the history table because it was autogenerated. Really appreciate your help. – Influential

Answer 4

/****** Object: Table [Intake].[MER_SF_Asset_Temporal] Script Date: 04/05/2021 12:33:57 PM ******/

ALTER TABLE [Intake].[MER_SF_Asset_Temporal] SET ( SYSTEM_VERSIONING = OFF  )
GO

/****** Object: Table [Intake].[MER_SF_Asset_Temporal] Script Date: 04/05/2021 12:33:57 PM ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Intake].[MER_SF_Asset_Temporal]') AND type in (N'U'))
DROP TABLE [Intake].[MER_SF_Asset_Temporal]
GO

/****** Object: Table [Intake].[MERSFAssetTemporalHistory] Script Date: 04/05/2021 12:33:58 PM ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Intake].[MERSFAssetTemporalHistory]') AND type in (N'U'))
DROP TABLE [Intake].[MERSFAssetTemporalHistory]
GO

Mastiff