如何删除SQL服务器数据库中的所有表?

I'm trying to write a script that will completely empty a SQL Server database. This is what I have so far:

我正在尝试编写一个完全清空SQL Server数据库的脚本。这是我目前所拥有的:

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

When I run it in the Management Studio, I get:

当我在管理工作室运行它时，我得到:

Command(s) completed successfully.

命令已成功完成。

but when I refresh the table list, they are all still there. What am I doing wrong?

但是当我刷新表列表时，它们仍然存在。我做错了什么?

13 个解决方案

#1

It doesn't work for me either when there are multiple foreign key tables.
I found that code that works and does everything you try (delete all tables from your database):

当有多个外键表时，它对我也不起作用。我发现代码可以运行并完成您尝试的所有事情(从数据库中删除所有表):

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + 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

You can find the post here. It is the post by Groker.

你可以在这里找到这个职位。这是Groker写的帖子。

#2

You can also delete all tables from database using only MSSMS tools (without using sql). Sometimes this way can be more comfortable (especially if it is performed occasionally)

您还可以使用MSSMS工具(不使用sql)从数据库中删除所有表。有时这种方式会更舒服(尤其是偶尔执行)

I do this step by step as follows:

我按以下步骤来做:

1. Select 'Tables' on the database tree (Object Explorer)
2. 在数据库树(对象资源管理器)上选择“Tables”
3. Press F7 to open Object Explorer Details view
4. 按F7打开对象资源管理器详细信息视图
5. In this view select tables which have to be deleted (in this case all of them)
6. 在此视图中，选择必须删除的表(在本例中是所有表)
7. Press Delete until all tables are deleted (you repeat it as many times as amount of errors due to key constraints/dependencies)
8. 按Delete直到所有表都被删除为止(由于关键约束/依赖项的原因，您要重复多次错误)

#3

delete is used for deleting rows from a table. You should use drop table instead.

delete用于从表中删除行。您应该使用drop table代替。

EXEC sp_msforeachtable 'drop table [?]'

#4

/* 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

#5

You are almost right, use instead:

你几乎是对的，用:

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

but second line you might need to execute more then once until you stop getting error:

但是第二行你可能需要执行更多，直到你停止错误:

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

Message:

信息:

Command(s) completed successfully.

means that all table were successfully deleted.

表示已成功删除所有表。

#6

Short and sweet:

简单明了的:

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

#7

Seems the command should be without the square blanket

看起来命令应该是没有方格的

EXEC sp_msforeachtable 'drop table ?'

#8

The accepted answer doesn't support Azure. It uses an undocumented stored procedure "sp_MSforeachtable". If you get an "azure could not find stored procedure 'sp_msforeachtable" error when running or simply want to avoid relying on undocumented features (which can be removed or have their functionality changed at any point) then try the below.

公认的答案并不支持Azure。它使用一个未文档化的存储过程“sp_MSforeachtable”。如果您获得了“azure无法在运行时发现存储过程的sp_msforeachtable”错误，或者仅仅希望避免依赖无文档的特性(可以删除或在任何时候改变其功能)，那么尝试下面的方法。

This version ignores the entity framework migration history table "__MigrationHistory" and the "database_firewall_rules" which is an Azure table you will not have permission to delete.

这个版本忽略了实体框架迁移历史表“__MigrationHistory”和“database_firewall_rules”，后者是一个Azure表，您没有删除的权限。

Lightly tested on Azure. Do check to make this this has no undesired effects on your environment.

轻轻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

Taken from:

来自:

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

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/

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

#9

How about dropping the entire database and then creating it again? This works for me.

删除整个数据库，然后再创建它，怎么样?这适合我。

DROP DATABASE mydb;
CREATE DATABASE mydb;

#10

The fasted way is:

禁食的方法是:

1. New Database Diagrams
2. 新的数据库关系图
3. Add all table
4. 添加所有的表
5. Ctrl + A to select all
6. Ctrl + A选择全部。
7. Right Click "Remove from Database"
8. 右键单击“从数据库中删除”
9. Ctrl + S to save
10. 按Ctrl + S保存
11. Enjoy
12. 享受

#11

I know this is an old post now but I have tried all the answers on here on a multitude of databases and I have found they all work sometimes but not all of the time for various (I can only assume) quirks of SQL Server.

我知道这是一个旧的帖子，但是我已经在大量的数据库上尝试了这里的所有答案，我发现它们有时都可以工作，但并不是一直都适用于SQL Server的各种怪癖(我只能假设)。

Eventually I came up with this. I have tested this everywhere (generally speaking) I can and it works (without any hidden store procedures).

最后我想到了这个。我在任何地方都测试过这个(一般来说)，它都可以工作(没有任何隐藏的存储过程)。

For note mostly on SQL Server 2014. (but most of the other versions I tried it also seems to worked fine).

主要用于SQL Server 2014。(但我尝试过的大多数其他版本似乎也很好用)。

I have tried while loops and nulls etc etc, cursors and various other forms but they always seem to fail on some databases but not others for no obvious reason.

我尝试过while循环和nulls等，游标和其他各种形式，但它们似乎总是在某些数据库上失败，而在其他数据库上却没有明显的原因。

Getting a count and using that to iterate always seems to work on everything Ive tested.

获得一个计数并使用它来迭代似乎总是对我测试过的所有东西都有效。

USE [****YOUR_DATABASE****]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- Drop all referential integrity constraints --
-- Drop all Primary Key constraints.          --

DECLARE @sql  NVARCHAR(296)
DECLARE @table_name VARCHAR(128)

DECLARE @constraint_name VARCHAR(128)
SET @constraint_name = ''

DECLARE @row_number INT

SELECT @row_number = Count(*) FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME = rc1.CONSTRAINT_NAME

WHILE @row_number > 0
BEGIN
    BEGIN
        SELECT TOP 1 @table_name = tc2.TABLE_NAME, @constraint_name = rc1.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
        LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME = rc1.CONSTRAINT_NAME
        AND rc1.CONSTRAINT_NAME > @constraint_name
        ORDER BY rc1.CONSTRAINT_NAME
        SELECT @sql = 'ALTER TABLE [dbo].[' + RTRIM(@table_name) +'] DROP CONSTRAINT [' + RTRIM(@constraint_name)+']'
        EXEC (@sql)
        PRINT 'Dropped Constraint: ' + @constraint_name + ' on ' + @table_name
        SET @row_number = @row_number - 1
    END
END
GO

-- Drop all tables --

DECLARE @sql  NVARCHAR(156)
DECLARE @name VARCHAR(128)
SET @name = ''

DECLARE @row_number INT

SELECT @row_number = Count(*) FROM sysobjects WHERE [type] = 'U' AND category = 0

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

#12

For me, the easiest way:

对我来说，最简单的方法是:

--First delete all constraints

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'
ALTER TABLE ' + QUOTENAME(s.name) + N'.'
+ QUOTENAME(t.name) + N' DROP CONSTRAINT '
+ QUOTENAME(c.name) + ';'
FROM sys.objects AS c
INNER JOIN sys.tables AS t
ON c.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s 
ON t.[schema_id] = s.[schema_id]
WHERE c.[type] IN ('D','C','F','PK','UQ')
ORDER BY c.[type];

EXEC sys.sp_executesql @sql;

-- Then drop all tables

exec sp_MSforeachtable 'DROP TABLE ?'

#13

In SSMS:

在地对地导弹:

• Right click the database
• 右键单击数据库
• Go to "Tasks"
• 去“任务”
• Click "Generate Scripts"
• 点击“生成脚本”
• In the "Choose Objects" section, select "Script entire database and all database objects"
• 在“选择对象”部分，选择“脚本整个数据库和所有数据库对象”
• In the "Set Scripting Options" section, click the "Advanced" button
• 在“设置脚本选项”部分，单击“高级”按钮
• On "Script DROP and CREATE" switch "Script CREATE" to "Script DROP" and press OK
• 在“脚本删除和创建”switch“脚本创建”到“脚本删除”并按OK
• Then, either save to file, clipboard, or new query window.
• 然后，保存到文件、剪贴板或新的查询窗口。
• Run script.
• 运行脚本。

Now, this will drop everything, including the database. Make sure to remove the code for the items you don't want dropped. Alternatively, in the "Choose Objects" section, instead of selecting to script entire database just select the items you want to remove.

现在，这将删除一切，包括数据库。确保删除不想删除的项目的代码。或者，在“选择对象”部分，而不是选择脚本整个数据库，只选择要删除的项。