如何使用一个 SQL 查询从数据库中删除所有表？

Question

提问by Majid

I don't want to type all tables' name to drop all of them. Is it possible with one query?

我不想输入所有表的名称来删除所有表。一个查询可以吗？

Answer 1

回答by P?????

Use the INFORMATION_SCHEMA.TABLESview to get the list of tables. Generate Drop scripts in the select statement and drop it using Dynamic SQL:

使用INFORMATION_SCHEMA.TABLES视图获取表列表。在 select 语句中生成 Drop 脚本并使用动态 SQL 将其删除：

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + '; '
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'

Exec Sp_executesql @sql

Sys.TablesVersion

Sys.Tables版本

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += ' Drop table ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + '; '
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'

Exec sp_executesql @sql

Note:If you have any foreign Keysdefined between tables then first run the below query to disableall foreign keyspresent in your database.

注意：如果您foreign Keys在表之间有任何定义，则首先运行以下查询以禁用foreign keys数据库中存在的所有内容。

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

For more information, check here.

有关更多信息，请查看此处。

Answer 2

回答by RageAgainstTheMachine

If you want to use only one SQL query to delete all tables you can use this:

如果您只想使用一个 SQL 查询来删除所有表，您可以使用以下命令：

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

This is a hidden Stored Procedure in sql server, and will be executed for each table in the database you're connected.

这是 sql server 中的一个隐藏存储过程，将为您连接的数据库中的每个表执行。

Note:You may need to execute the query a few times to delete all tables due to dependencies.

注意：由于依赖关系，您可能需要执行几次查询才能删除所有表。

Note2:To avoid the first note, before running the query, first check if there foreign keys relations to any table. If there are then just disableforeign key constraint by running the query bellow:

注意2：为了避免第一条，在运行查询之前，首先检查是否有任何表的外键关系。如果只有通过运行下面的查询来禁用外键约束：

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

Answer 3

回答by Dave.Gugg

If you don't want to type, you can create the statements with this:

如果您不想键入，可以使用以下命令创建语句：

USE Databasename

SELECT  'DROP TABLE [' + name + '];'
FROM    sys.tables

Then copy and paste into a new SSMS window to run it.

然后复制并粘贴到新的 SSMS 窗口中以运行它。

Answer 4

回答by Silthus

You could also use the following script to drop everything, including the following:

您还可以使用以下脚本删除所有内容，包括以下内容：

non-system stored procedures
views
functions
foreign key constraints
primary key constraints
tables

非系统存储过程
意见
职能
外键约束
主键约束
桌子

https://michaelreichenbach.de/how-to-drop-everything-in-a-mssql-database/

/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

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

WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

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

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

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

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

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all Foreign 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 = '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

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

Answer 5

回答by AeroX

I'd just make a small change to @NoDisplayName's answer and use QUOTENAME()on the TABLE_NAMEcolumn and also include the TABLE_SCHEMAcolumn encase the tables aren't in the dboschema.

我只是对@NoDisplayName 的答案做一个小改动，并QUOTENAME()在TABLE_NAME列上使用，并且还包括TABLE_SCHEMA将表不在dbo架构中的列。

DECLARE @sql nvarchar(max) = '';

SELECT @sql += 'DROP TABLE ' + QUOTENAME([TABLE_SCHEMA]) + '.' + QUOTENAME([TABLE_NAME]) + ';'
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE [TABLE_TYPE] = 'BASE TABLE';

EXEC SP_EXECUTESQL @sql;

Or using sysschema views (as per @swasheck's comment):

或者使用sys模式视图（根据@swasheck 的评论）：

DECLARE @sql nvarchar(max) = '';

SELECT @sql += 'DROP TABLE ' + QUOTENAME([S].[name]) + '.' + QUOTENAME([T].[name]) + ';'
FROM [sys].[tables] AS [T]
INNER JOIN [sys].[schemas] AS [S] ON ([T].[schema_id] = [S].[schema_id])
WHERE [T].[type] = 'U' AND [T].[is_ms_shipped] = 0;

EXEC SP_EXECUTESQL @sql;

Answer 6

回答by OMA

As a follow-up to Dave.Gugg's answer, this would be the code someone would need to get all the DROP TABLE lines in MySQL:

作为 Dave.Gugg 回答的后续，这将是某人在 MySQL 中获取所有 DROP TABLE 行所需的代码：

SELECT CONCAT('DROP TABLE ', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA = 'your_database_name'

Answer 7

回答by Supervision

The simplest way is to drop the whole database and create it once again:

最简单的方法是删除整个数据库并再次创建它：

drop database db_name
create database db_name

That's all.

就这样。

Answer 8

回答by Ani

If anybody else had a problem with best answer's solution (including disabling foreign keys), here is another solution from me:

如果其他人对最佳答案的解决方案（包括禁用外键）有疑问，这是我的另一个解决方案：

-- CLEAN DB
USE [DB_NAME]
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    EXEC sp_MSForEachTable 'DELETE FROM ?'

    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

    EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'

Answer 9

回答by xx1xx

Not quite 1 query, still quite short and sweet:

不完全是 1 查询，仍然很短和甜蜜：

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

Answer 10

回答by David Nyaoso

Use the following script to dropall constraints:

对drop所有人使用以下脚本constraints：

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += ' ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) +    ' NOCHECK CONSTRAINT all; '
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'

Exec Sp_executesql @sql

Then run the following to drop all tables:

然后运行以下命令删除所有表：

select @sql='';

SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + '; '
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'

Exec Sp_executesql @sql

This worked for me in Azure SQL Database where 'sp_msforeachtable'was not available!

这在'sp_msforeachtable'不可用的Azure SQL 数据库中对我有用！

如何使用一个 SQL 查询从数据库中删除所有表？

提问by Majid

回答by P?????

回答by RageAgainstTheMachine

回答by Dave.Gugg

回答by Silthus

回答by AeroX

回答by OMA

回答by Supervision

回答by Ani

回答by xx1xx

回答by David Nyaoso

相关推荐

最近更新

标签

如何使用一个 SQL 查询从数据库中删除所有表？

提问by Majid

回答by P?????

回答by RageAgainstTheMachine

回答by Dave.Gugg

回答by Silthus

回答by AeroX

回答by OMA

回答by Supervision

回答by Ani

回答by xx1xx

回答by David Nyaoso

相关推荐

Oracle SQL to_date & to_timestamp ORA-01858：在需要数字的位置找到非数字字符 & ORA-01850：小时必须介于 0 和 23 之间

在 ms-access 表中插入多行的 SQL 代码

SQL 如何在 PostgreSQL 上列出活动连接？

SQL 如何通过 plpgsql 从 Postgres 获取表的主键？

相关推荐

最近更新

标签