在SQL Server数据库中如何删除所有表？

Question

我正在尝试编写一个脚本，以完全清空SQL Server数据库。目前我有以下代码：

USE [dbname]
GO
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC sp_msforeachtable 'DELETE ?'

在 Management Studio 中运行时，我得到：

  

命令已成功完成。

但是当我刷新表列表时，它们仍然都在那里。我做错了什么吗？

- dixuji

---

如果这个页面上的解决方案都不起作用，也许你忘记了使用：USE [DatabaseName] GO。 - Serj Sagan

DELETE 会从表中删除记录。不过，你应该使用 DROP TABLE ?，但由于其他原因它可能无法起作用。 - datagod

必读：https://xkcd.com/327/ - crowne

Answer 1

您也可以仅使用MSSMS UI工具（而不使用SQL脚本）从数据库中删除所有表格。有时候这种方式会更加方便（特别是偶尔执行的情况下）。

我按以下步骤逐步进行：

1. 在数据库树（对象资源管理器）上选择“表”
2. 按F7打开对象资源管理器详细信息视图
3. 在此视图中选择需要删除的表格（在本例中为全部表格）
4. 一直按Delete键，直到所有表格都被删除（由于关键约束/依赖性错误的次数决定了重复操作的次数）

- Bronek

---

我无法在 Azure SQL 数据库上运行 sp_msforeachtable。我猜想他们不包括它在 Azure 中。这个解决方案非常好，适用于 EF 迁移时进行模拟或进行大量更改（重新开始）。 - trevorc

不考虑外键或顺序。 - Josh

这是一个方便的方法，如果你只需要偶尔执行，那么可以使用它，但如果你经常需要执行，则脚本可能更好。 - Dylan Hayes

@Josh 如果您在“删除”对话框中不断按“确定”按钮，计算顺序将不被考虑，它将继续删除所有可能的表格，最终会删除所有表格。请注意谨慎操作。 - clayRay

如果一个“视图”引用了一个“表”，那么持续按下“删除”键是无效的。 - F.I.V

Answer 2

当有多个外键表时，对我也不起作用。
我找到了一段能够工作并且可以做到你尝试的所有事情（从数据库中删除所有表）的代码:

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_SCHEMA + '].[' +  tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + '];'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec sp_executesql @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END

CLOSE @Cursor DEALLOCATE @Cursor
GO

EXEC sp_MSforeachtable 'DROP TABLE ?'
GO

您可以在这里找到Groker发布的帖子。

- Gabriel GM

---

为什么当您尝试使用BEGIN-END块将整个代码块包装在IF语句中时，它会抱怨光标？ - Adam

我遇到了错误：“找不到存储过程 'sp_MSForEachTable'。” - Korayem

如果您的表（带有约束）位于与 dbo 不同的模式中，则此答案无效。 如果您有自定义模式，则需要修改 sql 为：SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.CONSTRAINT_SCHEMA + '].[' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'。 - Asbjørn Ulsberg

sp_MSforeachtable在Azure中不可用。请使用@CountZero的答案代替。 - Ogglas

在网上发现了这个参考资料（不是我写的）：一个用于 Azure 的 sp_MSforeachtable 存储过程副本，使用 sp_MSforeach_worker：https://gist.github.com/metaskills/893599。 - João Vieira

Answer 3

在SSMS中：

• 右键单击数据库
• 转到“任务”
• 单击“生成脚本”
• 在“选择对象”部分，选择“脚本整个数据库和所有数据库对象”
• 在“设置脚本选项”部分，单击“高级”按钮
• 在“脚本删除和创建”上将“脚本创建”切换为“脚本删除”，然后按确定
• 然后，可以保存到文件、剪贴板或新查询窗口。
• 运行脚本。

现在这将删除所有内容，包括数据库。请确保删除您不想删除的项的代码。或者，在“选择对象”部分，选择要删除的项目而不是选择整个数据库进行脚本处理。

- Ben

---

如果您不想删除数据库（在我的情况下，我不想这样做，因为我会需要打电话支持来重新创建它），那么请使用“选择特定数据库对象”并选择您要删除的所有对象类型。请注意只选中需要删除的对象类型，不要删除整个数据库。 - d219

这是最简单的方法。 - Asiri Dissanayaka

这肯定是答案！ - Zac Taylor

对于任何想在SSMS 18中使用此选项的人，他们已经直接在右键菜单中添加了“生成脚本…”选项。 - Fjurg

Answer 4

被接受的答案不支持Azure。它使用了一个未记录的存储过程"sp_MSforeachtable"。如果在运行时出现"azure could not find stored procedure 'sp_msforeachtable"错误，或者想要避免依赖于未记录的功能（可以随时删除或更改其功能），请尝试以下方法。

此版本忽略了实体框架迁移历史表"__MigrationHistory"和"database_firewall_rules"，这是一个Azure表，您将无权删除。

在Azure上进行了轻微的测试。请务必检查以确保它不会对您的环境产生不良影响。

DECLARE @sql NVARCHAR(2000)

WHILE(EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
    SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
    EXEC(@sql)
    PRINT @sql
END

WHILE(EXISTS(SELECT * from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules'))
BEGIN
    SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules'
    EXEC(@sql)
    PRINT @sql
END

来源：

https://edspencer.me.uk/2013/02/25/drop-all-tables-in-a-sql-server-database-azure-friendly/

http://www.sqlservercentral.com/blogs/sqlservertips/2011/10/11/remove-all-foreign-keys/

- CountZero

---

在Azure上运行效果很好，比通过VS界面删除要快得多。 - Simeon Grigorovich

也适用于非Azure MS SQL数据库，谢谢！ - Micah Yeager

如果存在任何视图，它将进入无限循环。您必须添加一个额外的条件：AND TABLE_TYPE！= 'VIEW'。 - Marcelo Sader

Answer 5

delete用于从表中删除行。你应该使用drop table。

EXEC sp_msforeachtable 'drop table [?]'

- DaveShaw

---

那似乎解决了没有任何错误的问题，但现在我遇到了约束错误。我的 NOCHECK CONSTRAINT 行的语法有问题吗？ - dixuji

我在两个带有外键的表上使用了这个命令，它有效地运行了。执行以下命令：EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all' - DaveShaw

只有在我移除了方括号后，这个命令才起作用：EXEC sp_msforeachtable 'drop table ?'。 - LPains

在SQL Server中，如果任何表名包含任何奇怪的字符或空格，则需要使用括号。我不知道有哪个版本的SQL Server不支持在对象名称周围使用括号。 - DaveShaw

在 SQL Server 2012 (11.x) 中，我还必须删除方括号。 - Frieder

sp_msforeachtable【已经添加了括号】（至少在最近的版本中），因此它们应该从您的代码示例中删除。EXEC sp_msforeachtable 'drop table ?' 是正确的。 - Heinzi

Answer 6

你几乎是正确的，改用这个:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC sp_msforeachtable 'DROP TABLE ?'

但第二行可能需要执行多次，直到不再出现错误为止：

Could not drop object 'dbo.table' because it is referenced by a FOREIGN KEY constraint.

信息：

Command(s) completed successfully.

意味着所有的表格已成功删除。

- Michał Powaga

Answer 7

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

- Harpal

---

可以在SQL Azure中使用 :) sp_MSforeachtable在那里不起作用。 - Pavel Biryukov

请注意，如果您有非dbo模式（例如，如果您正在使用Hangfire），则此操作将失败。 - Liam Dawson

Answer 8

简短明了：

USE YOUR_DATABASE_NAME
-- Disable all referential integrity constraints
EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

-- Drop all PKs and FKs
declare @sql nvarchar(max)
SELECT @sql = STUFF((SELECT '; ' + 'ALTER TABLE ' + Table_Name  +'  drop constraint ' + Constraint_Name  from Information_Schema.CONSTRAINT_TABLE_USAGE ORDER BY Constraint_Name FOR XML PATH('')),1,1,'')
EXECUTE (@sql)
GO

-- Drop all tables
EXEC sp_MSforeachtable 'DROP TABLE ?'
GO

- xx1xx

---

我不得不将 sp_msforeachtable 替换为 sp_MSforeachtable，并强烈建议在第一行添加 use yourdatabase。 非常顺利地完成了。 - Simon Sobisch

简短而简洁！谢谢。 - sapatelbaps

Answer 9

一针见血！

您可以使用以下查询从数据库中删除所有表格：

执行 sp_MSforeachtable @command1 = “DROP TABLE ?”

祝编码愉快！

- Gajanan Kulkarni

---

执行了多次，最终删除了所有的表格。 - JEEVAN GEORGE ANTONY

我认为你只需要做一次。 - Gajanan Kulkarni

我有多个带有外键的表格。 在单次执行中，它没有起作用。 但是多次执行后，我成功删除了所有表格。 - JEEVAN GEORGE ANTONY

Answer 10

最快的方法是：

1. 创建新的数据库图
2. 添加所有的表格
3. 按下Ctrl + A全选
4. 右键单击"从数据库中删除"
5. 按下Ctrl + S保存
6. 享受使用吧

- TuanDPH

---

这绝对是最快、最少出错的方法。这应该被接受为答案。 - DARKGuy

也许是因为有很多表时它会崩溃。 - AriesConnolly

这个选项似乎不再可用。 - chaostheory