求助：SQL Server 2016编写自动删除指定日期前空表的脚本

核心思路 #

要实现删除早于指定日期的空表，需同时筛选两类符合条件的表：

• 创建时间早于目标日期的表
• 无数据（总行数为0）的表

通过查询系统视图sys.tables获取表的创建时间，结合sys.partitions统计表的行数，再生成动态DROP TABLE语句执行删除操作。

具体脚本 #

-- 设置要保留的最小创建日期（格式：YYYY-MM-DD）
DECLARE @CutoffDate DATE = '2024-01-01'

-- 生成删除空表的动态SQL
DECLARE @DropSQL NVARCHAR(MAX) = ''

SELECT @DropSQL += 'DROP TABLE [' + SCHEMA_NAME(schema_id) + '].[' + name + '];' + CHAR(10)
FROM sys.tables t
JOIN sys.partitions p ON t.object_id = p.object_id
WHERE t.create_date < @CutoffDate
  AND p.index_id IN (0, 1) -- 仅统计堆表或聚集索引，避免重复计算行数
  AND p.rows = 0
  AND t.is_ms_shipped = 0 -- 排除系统自带表

-- 先打印生成的SQL，确认无误后再执行实际删除
PRINT @DropSQL
-- EXEC sp_executesql @DropSQL

关键注意事项 #

• 测试优先：先执行PRINT查看生成的删除语句，确认没有误选重要表后，再取消注释EXEC执行删除。
• 权限要求：执行脚本的账号需具备ALTER权限（用于删除表）和查询系统视图的权限。
• 分区表适配：若数据库存在分区表，需调整sys.partitions的筛选逻辑，确保准确统计总行数。

配置定时执行策略（SQL Server代理作业） #

1. 在SQL Server Management Studio中，展开SQL Server代理 -> 作业，右键选择新建作业。
2. 在常规选项卡设置作业名称，比如“定期清理过期空表”。
3. 切换到步骤选项卡，新建步骤：
   • 步骤名称：生成并执行删除脚本
   • 类型：Transact-SQL脚本(T-SQL)
   • 数据库：选择目标业务数据库
   • 命令框粘贴上述脚本（确保已启用EXEC语句）
4. 切换到计划选项卡，新建执行计划，设置频率（比如每周日凌晨1点）。
5. 保存作业后，系统会自动按计划执行清理操作。

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