如何自动删除SQL Server中无需保留的架构及下属表？

完全可以实现自动化操作，以下是一套完整的脚本方案，涵盖删除多余架构下的所有对象（不止表，还包括视图、存储过程等），再删除架构本身的流程：

步骤1：定义要保留的架构列表 #

首先创建一个表变量，把你需要保留的500个架构名称填进去：

-- 定义需保留的架构列表，替换为你的500个架构名称
DECLARE @KeepSchemas TABLE (SchemaName NVARCHAR(128) PRIMARY KEY);
INSERT INTO @KeepSchemas (SchemaName)
VALUES ('架构名称1'), ('架构名称2'); -- 继续添加剩余需保留的架构

步骤2：批量删除多余架构下的所有对象 #

仅删除表可能不足以删除架构（架构下可能还有视图、存储过程等对象），所以需要先清理所有对象：

DECLARE @DropObjectsSQL NVARCHAR(MAX) = '';

-- 删除用户表
SELECT @DropObjectsSQL += 'DROP TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ';' + CHAR(13) + CHAR(10)
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type = 'U' 
AND s.name NOT IN (SELECT SchemaName FROM @KeepSchemas);

-- 删除视图
SELECT @DropObjectsSQL += 'DROP VIEW ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ';' + CHAR(13) + CHAR(10)
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type = 'V' 
AND s.name NOT IN (SELECT SchemaName FROM @KeepSchemas);

-- 删除存储过程
SELECT @DropObjectsSQL += 'DROP PROCEDURE ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ';' + CHAR(13) + CHAR(10)
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type = 'P' 
AND s.name NOT IN (SELECT SchemaName FROM @KeepSchemas);

-- 删除用户定义函数（标量、内联表值、表值函数）
SELECT @DropObjectsSQL += 'DROP FUNCTION ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ';' + CHAR(13) + CHAR(10)
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type IN ('FN', 'IF', 'TF') 
AND s.name NOT IN (SELECT SchemaName FROM @KeepSchemas);

-- 执行删除操作
IF @DropObjectsSQL <> ''
BEGIN
    PRINT '正在清理多余架构下的对象...';
    EXEC sp_executesql @DropObjectsSQL;
END

步骤3：批量删除多余架构 #

清理完对象后，就可以删除多余的架构了（注意排除系统架构，避免误删）：

DECLARE @DropSchemasSQL NVARCHAR(MAX) = '';

SELECT @DropSchemasSQL += 'DROP SCHEMA ' + QUOTENAME(s.name) + ';' + CHAR(13) + CHAR(10)
FROM sys.schemas s
WHERE s.name NOT IN (SELECT SchemaName FROM @KeepSchemas)
-- 排除系统默认架构，请勿删除
AND s.name NOT IN ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA');

-- 执行删除架构操作
IF @DropSchemasSQL <> ''
BEGIN
    PRINT '正在删除多余架构...';
    EXEC sp_executesql @DropSchemasSQL;
END

注意事项 #

• 先测试再执行：务必在测试环境验证脚本逻辑，确认不会误删需保留的架构和对象
• 备份数据库：操作前完全备份数据库，防止意外失误
• 补充其他对象类型：如果架构下还有触发器（type='TR'）、同义词（type='SN'）等对象，可参照上述格式添加对应的DROP语句
• 权限要求：执行脚本的账号需要具备ALTER ANY SCHEMA、DROP ANY TABLE、DROP ANY VIEW等相关权限

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