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

我试图写一个脚本，将完全清空SQL Server数据库。这是我目前所拥有的:

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

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

命令执行成功。

但当我刷新表列表时，它们都还在那里。我做错了什么?

413054 次浏览
小开

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

EXEC sp_msforeachtable 'drop table [?]'
小开
最佳答案
当有多个外键表时，它也不适合我。
我发现，工作的代码，并做一切你尝试(删除所有表从你的数据库):

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

你可以找到帖子在这里。这是格罗克的帖子。

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

你差不多说对了，可以用:

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.

表示已成功删除所有表。

小开

您也可以只使用MSSMS UI工具(不使用SQL脚本)从数据库中删除所有表。有时这种方式会更舒服(尤其是偶尔这样做的时候)

我是这样一步一步做的:

  1. 在数据库树中选择“Tables”(对象资源管理器)
  2. 按F7打开对象资源管理器详细信息视图
  3. 在这个视图中选择必须删除的表(在本例中是所有表)
  4. 一直按Delete键，直到所有表都被删除(重复该操作的次数与由于键约束/依赖导致的错误数量相同)
小开

如果删除整个数据库，然后重新创建它呢?这对我很有用。

DROP DATABASE mydb;
CREATE DATABASE mydb;
小开

看来这个命令应该不带方巾

EXEC sp_msforeachtable 'drop table ?'
小开

简短而甜蜜:

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
小开

禁食的方式是:

  1. 新的数据库图表
  2. 添加所有表
  3. 按Ctrl + A全选
  4. 右键单击“从数据库删除”
  5. 按Ctrl + S保存
  6. 享受
小开

已接受的答案不支持Azure。它使用一个无文档的存储过程“sp_MSforeachtable”。如果您在运行时遇到“azure无法找到存储过程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

来自:

< a href = " https://edspencer.me。英国/ 2013/02/25 / drop-all-tables-in-a-sql-server-database-azure-friendly / noreferrer“rel = > 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/

小开

我知道这是一篇老文章了，但我已经在大量的数据库上尝试了所有的答案，我发现它们有时都有效，但不是所有时间都适用于SQL Server的各种(我只能假设)怪癖。

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

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

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

获得一个计数并使用它进行迭代似乎总是适用于我测试的所有内容。

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
小开

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

--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 ?'
小开

在地对地导弹:

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

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

小开

对于时态表，由于可能会有一些外键和异常，所以它有点复杂:

Drop table operation failed on table XXX because it is not a supported operation on system-versioned temporal tables

你可以使用的是:

-- Disable constraints (foreign keys)
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO


-- Disable system versioning (temporial tables)
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableTemporalType'') = 2
ALTER TABLE ? SET (SYSTEM_VERSIONING = OFF)
'
GO


-- Removing tables
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO
小开

现货! !

你可以使用下面的查询从数据库中删除所有的表

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

编码愉快!

小开

Azure SQL +表(带约束)的模式与dbo + ipv6_database_firewall_rules条件不同。

这是https://stackoverflow.com/a/43128914/4510954 answer的一个小扩展。

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 ' + CONSTRAINT_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' AND TABLE_NAME != 'ipv6_database_firewall_rules'))
BEGIN
SELECT TOP 1 @sql=('DROP TABLE ' + CONSTRAINT_SCHEMA  + '.[' + TABLE_NAME + ']')
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules'
EXEC(@sql)
PRINT @sql
END
小开

sp_msforeachtable在Azure SQL中不可用

对于Azure SQL:

此查询将删除外键约束

DECLARE @Name VARCHAR(200)
DECLARE @Constraint VARCHAR(300)
DECLARE @SQL VARCHAR(300)


SELECT @Name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN 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 = 'FOREIGN 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 FK Constraint: ' + @Constraint + ' on ' + @Name
SELECT @Constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN 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 = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

这将从数据库中删除所有表

DECLARE @Name VARCHAR(200)
DECLARE @SQL VARCHAR(300)


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) +']' /*here you can change schema if it is different from dbo*/
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

提交答案