Sql Server 小技巧 —— 批量生成数据库表删除语句

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; -- 确认输出正确后可执行这行代码