MSSQL快速清空数据库

最新推荐文章于 2024-05-27 12:54:44 发布

weixin_34161083

最新推荐文章于 2024-05-27 12:54:44 发布

阅读量243

点赞数

原文链接：https://my.oschina.net/bery/blog/79391

版权

为什么80%的码农都做不了架构师？>>>

--禁用所有外键
DECLARE @disablefk AS nvarchar(max)
SET @disablefk = '';
SELECT @disablefk = @disablefk + 'ALTER TABLE ['  + TABLE_NAME + '] NOCHECK CONSTRAINT [' +  CONSTRAINT_NAME + '];'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY';
EXEC sp_executesql @disablefk
GO

--删除所有数据
DECLARE @del AS nvarchar(max)
SET @del = '';
SELECT @del = @del + 'DELETE FROM ' + TABLE_NAME + ';'
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'
EXEC sp_executesql @del
GO

--启用所有外键
DECLARE @enablefk AS nvarchar(max)
SET @enablefk = '';
SELECT @enablefk = @enablefk + 'ALTER TABLE ['  + TABLE_NAME + '] CHECK CONSTRAINT [' +  CONSTRAINT_NAME + '];'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY';
EXEC sp_executesql @enablefk
GO

转载于:https://my.oschina.net/bery/blog/79391