All we need is an easy explanation of the problem, so here it is.
I’m trying to drop a temporal (system-versioned) table in SQL Server. I used a regular DROP TABLE statement.
DROP TABLE [schema].
GO
This raised the following error:
Drop table operation failed on table ‘[database].[schema].
‘
because it is not a supported operation on system-versioned temporal
tables.
How do you drop a temporal table in SQL Server?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
To drop a system versioned table you first have to turn off system versioning.
CREATE TABLE [temporal]([id] int IDENTITY(1, 1)
PRIMARY KEY CLUSTERED,
[ValidFrom] datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN
NOT NULL,
[ValidTo] datetime2(7) GENERATED ALWAYS AS ROW END HIDDEN
NOT NULL,
PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])) WITH(SYSTEM_VERSIONING = ON(HISTORY_TABLE = [dbo].[Temporal_History]));
drop table temporal;
this will result in
Drop table operation failed on table ‘tempdb.dbo.temporal’ because it is not a supported operation on system-versioned temporal tables.
if you turn off system version you will be able to drop table. dont forget to drop the history table as well if you no longer need it
ALTER TABLE dbo.temporal SET ( SYSTEM_VERSIONING = Off )
drop table temporal;
drop table Temporal_History
Method 2
As mentioned, you need to turn SYSTEM_VERSIONING off. You should also drop the history table.
To do this in an automated fashion in a script, use the following
DECLARE @schema sysname = 'dbo';
DECLARE @table sysname = 'YourTable';
DECLARE @historyTable nvarchar(500) = (
SELECT QUOTENAME(sHist.name) + '.' + QUOTENAME(tHist.name)
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.tables tHist ON tHist.object_id = t.history_table_id
JOIN sys.schemas sHist ON sHist.schema_id = tHist.schema_id
WHERE s.name = @schema
AND t.name = @table
);
DECLARE @sql nvarchar(max);
IF @historyTable IS NOT NULL
SET @sql = '
ALTER TABLE ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
SET ( SYSTEM_VERSIONING = OFF );
DROP TABLE ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ';
DROP TABLE ' + @historyTable + ';';
ELSE
SET @sql = 'DROP TABLE ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ';';
EXEC (@sql);
Method 3
This also could be wrapped up in a sp:
BEGIN TRY
BEGIN TRAN;
DECLARE @TableName VARCHAR(200),
@SchemaName VARCHAR(50) = 'dbo',
@HistoryTableName VARCHAR(200),
@HistoryTableSchema VARCHAR(50) = 'dbo',
@DropFkScript NVARCHAR(MAX) = N'',
@DisableVersioningScript NVARCHAR(MAX) = N'',
@DropTemporaryTableScript NVARCHAR(MAX) = N'',
@DropTableSql NVARCHAR(MAX) = N'';
SET @TableName = 'yourtable';
SET @SchemaName = 'yourschema';
--check if the table exists
IF EXISTS
(
SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @SchemaName
AND TABLE_NAME = @TableName
)
BEGIN
--check if the table has fk's
IF EXISTS
(
SELECT 1
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
)
BEGIN
SELECT @DropFkScript
= STRING_AGG(
'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].['
+ OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']',
';'
)
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID(@SchemaName + '.' + @TableName);
--drop fk's
EXEC sp_executesql @stmt = @DropFkScript;
END;
--check if the table is versioned
IF EXISTS
(
SELECT 1
FROM sys.tables p
INNER JOIN sys.tables h
ON p.history_table_id = h.object_id
WHERE p.[name] = @TableName
AND p.schema_id = SCHEMA_ID(@SchemaName)
AND p.temporal_type_desc = 'SYSTEM_VERSIONED_TEMPORAL_TABLE'
)
BEGIN
SELECT @HistoryTableName = h.[name],
@HistoryTableSchema = s.[name]
FROM sys.tables t
JOIN sys.tables h
ON t.history_table_id = h.object_id
JOIN sys.schemas s
ON h.schema_id = s.schema_id
WHERE t.temporal_type = 2
AND SCHEMA_NAME(t.schema_id) = @SchemaName
AND t.[name] = @TableName;
SET @DisableVersioningScript
= N'ALTER TABLE ' + @SchemaName + N'.' + @TableName + N' SET ( SYSTEM_VERSIONING = OFF);';
SET @DropTemporaryTableScript = N'DROP TABLE ' + @HistoryTableSchema + N'.' + @HistoryTableName + N';';
--drop history table
EXEC sp_executesql @stmt = @DisableVersioningScript;
EXEC sp_executesql @stmt = @DropTemporaryTableScript;
END;
--drop main table
SET @DropTableSql = N'DROP TABLE ' + @SchemaName + N'.' + @TableName + N';';
EXEC sp_executesql @stmt = @DropTableSql;
END;
COMMIT TRAN;
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0