sqlserver批量删除表

分表数据库中的分表都是以 WaterDay_ 开头的，现需要批量删除

-- 声明变量
DECLARE @TableName NVARCHAR(255);
DECLARE @SQL NVARCHAR(MAX);

-- 创建游标，遍历符合条件的表
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' -- 仅选择用户表
  AND TABLE_NAME LIKE 'WaterDay_%'; -- 匹配以 WaterDay_ 开头的表

OPEN TableCursor;

FETCH NEXT FROM TableCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 构造 DROP TABLE 语句
    SET @SQL = 'DROP TABLE [' + @TableName + ']';
    PRINT @SQL; -- 打印语句以供检查
    EXEC sp_executesql @SQL; -- 执行语句

    FETCH NEXT FROM TableCursor INTO @TableName;
END;

CLOSE TableCursor;
DEALLOCATE TableCursor;