Sql Server 小技巧 批量生成数据库表删除语句
-- Step 1: 删除外键约束 DECLARE @fk_sql NVARCHAR(MAX) = N'';
SELECT
@fk_sql + = 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(fk.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';' + CHAR(13)
FROM
sys.foreign_keys AS fk
INNER JOIN sys.tables AS t ON fk.referenced_object_id = t.object_id
WHERE
t.name LIKE '数据库表名';
-- 替换为你想删除的表名前缀
PRINT @fk_sql;
-- EXEC sp_executesql @fk_sql; -- 确认输出正确后可执行这行代码
-- Step 2: 删除表 DECLARE @sql NVARCHAR(MAX) = N'';
SELECT
@sql + = 'DROP TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';' + CHAR(13)
FROM
sys.tables
WHERE
name LIKE '数据库表名';
-- 替换为你想删除的表名前缀
PRINT @sql;
-- EXEC sp_executesql @sql; -- 确认输出正确后可执行这行代码