SQL Server删除非系统版本控制历史表数据仍报错如何解决？

• 首先明确TableTemporalType的返回值规则：
  • 0：普通非时态表
  • 1：系统版本控制时态表的历史表（不可直接执行DELETE操作）
  • 2：开启了系统版本控制的时态主表（直接执行DELETE也会报错，因为关联了历史表）
• 你现有脚本的两个核心问题：
  1. 仅排除了值为1的历史表，没有排除值为2的时态主表，直接删除主表也会触发关联历史表的报错
  2. 未公开存储过程sp_MSForEachTable存在遍历不稳定、参数替换异常的概率，可能导致判断逻辑失效误删历史表

方案1：仅清理普通表数据，完全跳过所有时态相关表（无需修改时态配置） #

适用于不需要清理时态主表数据的场景，逻辑简单安全：

-- 禁用所有表约束
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
-- 仅清理普通表数据，跳过时态主表和历史表
EXEC sp_MSforeachtable '
SET QUOTED_IDENTIFIER ON;
DECLARE @TableType INT = OBJECTPROPERTY(OBJECT_ID(''?''), ''TableTemporalType'')
IF @TableType = 0
BEGIN
    DELETE FROM ?
END
'
GO
-- 恢复所有表约束
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
GO

方案2：需要清理时态主表数据，临时修改时态配置 #

适用于需要清空时态主表数据的集成测试场景，可自行选择是否同步清空历史表：

DECLARE @TableName NVARCHAR(500), @HistoryTableName NVARCHAR(500), @SQL NVARCHAR(MAX)

-- 遍历所有时态主表处理
DECLARE temporal_cursor CURSOR FOR
SELECT 
    t.name AS TableName,
    h.name AS HistoryTableName
FROM sys.tables t
JOIN sys.tables h ON t.history_table_id = h.object_id
WHERE t.temporal_type = 2

OPEN temporal_cursor
FETCH NEXT FROM temporal_cursor INTO @TableName, @HistoryTableName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 临时关闭系统版本控制
    SET @SQL = N'ALTER TABLE [dbo].[' + @TableName + N'] SET (SYSTEM_VERSIONING = OFF)'
    EXEC sp_executesql @SQL

    -- 清理主表数据，若需要清空历史表可取消注释下两行
    -- SET @SQL = N'DELETE FROM [dbo].[' + @HistoryTableName + N']'
    -- EXEC sp_executesql @SQL
    SET @SQL = N'DELETE FROM [dbo].[' + @TableName + N']'
    EXEC sp_executesql @SQL

    -- 重新开启系统版本控制，保留原有配置
    SET @SQL = N'ALTER TABLE [dbo].[' + @TableName + N'] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[' + @HistoryTableName + N'], DATA_CONSISTENCY_CHECK = OFF))'
    EXEC sp_executesql @SQL

    FETCH NEXT FROM temporal_cursor INTO @TableName, @HistoryTableName
END

CLOSE temporal_cursor
DEALLOCATE temporal_cursor

-- 清理所有普通表数据
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSforeachtable '
SET QUOTED_IDENTIFIER ON;
IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableTemporalType'') = 0
DELETE FROM ?
'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
GO

如果希望完全规避sp_MSForEachTable的不稳定问题，可直接通过系统表sys.tables遍历实现逻辑，稳定性更高：

DECLARE @SQL NVARCHAR(MAX) = N''
-- 禁用普通表约束
SELECT @SQL += N'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(name) + N' NOCHECK CONSTRAINT ALL;' + CHAR(13)
FROM sys.tables WHERE temporal_type = 0
EXEC sp_executesql @SQL

-- 清理普通表数据
SET @SQL = N''
SELECT @SQL += N'DELETE FROM ' + QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(name) + N';' + CHAR(13)
FROM sys.tables WHERE temporal_type = 0
EXEC sp_executesql @SQL

-- 恢复普通表约束
SET @SQL = N''
SELECT @SQL += N'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(name) + N' WITH CHECK CHECK CONSTRAINT ALL;' + CHAR(13)
FROM sys.tables WHERE temporal_type = 0
EXEC sp_executesql @SQL

内容的提问来源于stack exchange，提问作者Alaa Masoud