SQL DROP TABLE语句概述

This article gives an overview of the SQL DROP TABLE statement to remove one or more tables from a database.

本文概述了从数据库中删除一个或多个表SQL DROP TABLE语句。

In my earlier article, Difference between SQL Truncate and SQL Delete statements in SQL Server, we explored to delete data from an existing data. We might delete whole data using both SQL Delete and SQL Truncate statements. We might also delete specific data from the SQL Server tables using SQL Delete statement. SQL Delete and Truncate do not move the object structure from the database.

在我之前的文章SQL Server中SQL Truncate和SQL Delete语句之间的差异中 ，我们探讨了从现有数据中删除数据。 我们可能同时使用SQL Delete和SQL Truncate语句删除整个数据。 我们还可以使用SQL Delete语句从SQL Server表中删除特定数据。 SQL Delete和Truncate不会从数据库中移动对象结构。

Sometimes, we do want to perform database clean up by removing unnecessary tables. Let’s say you want to make bulk changes to a table. Most of the DBA’s take table-level backup before making any change to it. It involves creating another backup table in a similar database with a different name.

有时，我们确实希望通过删除不必要的表来执行数据库清理。 假设您要对表格进行批量更改。 大多数DBA在进行任何更改之前都进行表级备份。 它涉及在具有不同名称的相似数据库中创建另一个备份表。

In the following table, we want to delete multiple records.

在下表中，我们要删除多个记录。

SELECT [BusinessEntityID]
      ,[NationalIDNumber]
      ,[LoginID]
      ,[OrganizationNode]
      ,[OrganizationLevel]
      ,[JobTitle]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[Gender]
      ,[HireDate]
      ,[SalariedFlag]
      ,[VacationHours]
      ,[SickLeaveHours]
      ,[CurrentFlag]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2017].[HumanResources].[Employee]

Before removing the data, take a backup using SELECT INTO command.

删除数据之前，请使用SELECT INTO命令进行备份。

SELECT [BusinessEntityID], 
       [NationalIDNumber], 
       [LoginID], 
       [OrganizationNode], 
       [OrganizationLevel], 
       [JobTitle], 
       [BirthDate], 
       [MaritalStatus], 
       [Gender], 
       [HireDate], 
       [SalariedFlag], 
       [VacationHours], 
       [SickLeaveHours], 
       [CurrentFlag], 
       [rowguid], 
       [ModifiedDate]
INTO [AdventureWorks2017].[HumanResources].[Employee13072019]
FROM [AdventureWorks2017].[HumanResources].[Employee];

It created another table with existing column structure and copies the data into it. We need to perform regular clean-up of these backup tables. It takes unnecessary disk space and if you do index maintenance for all indexes, it might add extra overhead to the system.

它使用现有的列结构创建了另一个表，并将数据复制到该表中。 我们需要定期清理这些备份表。 这会占用不必要的磁盘空间，并且如果对所有索引进行索引维护，则可能会增加系统的额外开销。

Let’s explore the SQL DROP TABLE in the next action.

让我们在下一个动作中探索SQL DROP TABLE。

SQL DROP TABLE概述 (Overview of SQL DROP TABLE)

We use the SQL DROP Table command to drop a table from the database. It completely removes the table structure and associated indexes, statistics, permissions, triggers and constraints. You might have SQL Views and Stored procedures referencing to the SQL table. SQL Server does not remove these stored procedures and views. We need to drop them explicitly. We should check object dependencies before removing a SQL table.

我们使用SQL DROP Table命令从数据库中删除表。 它完全删除了表结构以及相关的索引，统计信息，权限，触发器和约束。 您可能具有引用SQL表SQL视图和存储过程。 SQL Server不会删除这些存储过程和视图。 我们需要明确地删除它们。 我们应该在删除SQL表之前检查对象的依赖性。

SQL DROP TABLE的语法 (The syntax for SQL DROP TABLE)

DROP TABLE [database_name].[schema_name].[table_name]

It uses the following parameters.

它使用以下参数。

• Database_name: Specify the database name in which table exists. We can skip this parameter if we execute the drop command in the current database context Database_name ：指定表所在的数据库名称。 如果在当前数据库上下文中执行drop命令，则可以跳过此参数
• Schema_name: Specify the schema name for which the object exists. If the object belongs to the default schema DBO, we can skip this parameter. SQL Server automatically uses dbo schema. We must specify the schema name if the object belongs other than the default schema Schema_name：指定对象所在的架构名称。 如果对象属于默认架构DBO，则可以跳过此参数。 SQL Server自动使用dbo架构。 如果对象属于默认模式，则必须指定模式名称
• Table name: Specify the table that we want to remove 表格名称：指定我们要删除的表格

示例1：使用SQL DROP Table语句删除单个表 (Example 1: Drop a single table using the SQL DROP Table statement)

Execute the following query to drop HumanResources.Employee13072019 table from the AdventureWorks2017 database.

执行以下查询以从AdventureWorks2017数据库中删除HumanResources.Employee13072019表。

Drop table Employee13072019

It gives the following error message. We get this error because the object belongs to the default schema dbo.

它给出以下错误信息。 我们收到此错误，因为该对象属于默认架构dbo。

To fix this, we need to specify schema name along with the table name.

要解决此问题，我们需要指定架构名称以及表名称。

Drop table AdventureWorks2017.HumanResources.Employee13072019

Alternatively, we can use the following query to drop a SQL table.

或者，我们可以使用以下查询删除SQL表。

Use AdventureWorks2017 
Go
Drop table HumanResources.Employee13072019

示例2：使用SQL DROP Table语句将多个表放在一起 (Example 2: Drop multiple tables together using the SQL DROP Table statement)

We can drop multiple tables together using a single DROP Table statement as well.

我们也可以使用一个DROP Table语句将多个表放在一起。

Let’s create three tables and later we will drop it.

让我们创建三个表，稍后我们将其删除。

CREATE TABLE Sam(id INT);
CREATE TABLE Amp(id INT);
CREATE TABLE Rmp(id INT);

Now, we can use the following drop table statement and specify all table names together to drop it.

现在，我们可以使用以下drop table语句，并一起指定所有表名以将其删除。

DROP TABLE Sam, Amp, Rmp;

示例2：使用SQL DROP Table语句删除具有外键约束SQL表 (Example 2: Drop a SQL table having a foreign key constraint using the SQL DROP Table statement)

In SQL Server, we can use a foreign key between multiple table columns to link data between these tables. We cannot drop the table directly in this case.

在SQL Server中，我们可以在多个表列之间使用外键来链接这些表之间的数据。 在这种情况下，我们不能直接删除表。

Let’s understand this using an example. We will create two SQL tables Department and Employee. In the database diagram, you can see that we have a foreign key constraint for on the Dept_id column

让我们通过一个例子来理解这一点。 我们将创建两个SQL表Department和Employee。 在数据库图中，您可以看到Dept_id列上有一个外键约束

Execute the following script to create both tables.

执行以下脚本来创建两个表。

CREATE TABLE Department
(Dept_id   INT IDENTITY PRIMARY KEY, 
 Dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE Employee1
(EmpID   INT IDENTITY PRIMARY KEY, 
 EmpName VARCHAR(50) NOT NULL, 
 Dept_id INT NOT NULL, 
 FOREIGN KEY(Dept_id) REFERENCES department(dept_id)
);

Let’s try to drop departments table using SQL DROP TABLE statement.

让我们尝试使用SQL DROP TABLE语句删除部门表。

Drop table Department

You get the following error message.

您收到以下错误信息。

Foreign key relationships are like a parent-child relationship. We cannot delete a parent table that is referenced by a foreign key constraint. We need to either remove the foreign key relationship or drop the child table first.

外键关系就像是父子关系。 我们无法删除外键约束所引用的父表。 我们需要删除外键关系或先删除子表。

In my example, we need to drop the Employee1 table first because it has a foreign key relationship with the department table.

在我的示例中，我们需要首先删除Employee1表，因为它与Department表具有外键关系。

Drop table Employee1
DROP TABLE Department;

We can use a single SQL Drop Table statement as well in such case with a caution to drop the referencing table first.

在这种情况下，我们也可以使用单个SQL Drop Table语句，但请注意先删除引用表。

DROP TABLE Employee1, Department;

示例3：使用SQL DROP Table语句删除临时表 (Example 3: Drop a temp table using the SQL DROP Table statement)

We can also drop a temp table in a way similar to the regular table. The following example creates a temp table and drops it.

我们还可以以类似于常规表的方式删除临时表。 下面的示例创建一个临时表并将其删除。

CREATE TABLE #temp1(col1 INT);  
GO  
INSERT INTO #temp1
VALUES(100);  
GO  
DROP TABLE #temp1;

示例4：使用IF EXISTS删除表 (Example 4: Dropping a table using IF EXISTS)

Usually, developers check for the existence of any database object in the database and drop the object if it exists.

通常，开发人员会检查数据库中是否存在任何数据库对象，如果存在则将其删除。

If we try to drop a table that does not exist, we get the following error message.

如果我们尝试删除不存在的表，则会收到以下错误消息。

We do not want any error in executing queries, especially during the execution of a bunch of code. Before SQL Server 2016, developers use the IF EXISTS statement and check for the object existence before dropping it.

我们不希望在执行查询时出现任何错误，尤其是在执行一堆代码期间。 在SQL Server 2016之前，开发人员使用IF EXISTS语句并在删除对象之前检查对象是否存在。

For example, in the following query, we check the department table in the sys .objects.

例如，在以下查询中，我们检查sys .objects中的部门表。

If the object exists, execute the drop table statement else, no actions required.

如果对象存在，则执行drop table语句，否则无需任何操作。

IF EXISTS
(
    SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'Department')
          AND type IN(N'U')
)
    DROP TABLE Department;
GO

Alternatively, we can check the object id of the SQL table and execute the drop table statement if it is not NULL.

或者，我们可以检查SQL表的对象ID，如果它不为NULL，则执行drop table语句。

IF OBJECT_ID('Department', 'U') IS NOT NULL
    DROP TABLE Department;  
GO

These approaches work fine. However, the issue is that you need to write a long transact SQL code. Starting from SQL Server 2016, we can use the new syntax of SQL DROP Table. It drops a SQL table if it already exists.

这些方法很好用。 但是，问题是您需要编写一个长事务SQL代码。 从SQL Server 2016开始，我们可以使用SQL DROP Table的新语法。 如果它已经存在，它将删除一个SQL表。

DROP Table IF EXISTS  Table_name

It is a short version of the code we executed before. Let’s try to drop the department table using this new code. It is a small and easy way to drop a table.

这是我们之前执行的代码的简短版本。 让我们尝试使用此新代码删除部门表。 这是删除表的一种小而简单的方法。

DROP TABLE IF EXISTS Department;

示例5：在存储过程，视图中删除具有引用的表 (Example 5: Dropping a table having a reference in the stored procedures, views)

SQL Server does not give any error message if you drop a SQL table that is being used in the stored procedure, views. We can use the SCHEMABINDING option, but it is not in the scope of this article.

如果删除存储过程视图中正在使用SQL表，SQL Server不会给出任何错误消息。 我们可以使用SCHEMABINDING选项，但这不在本文讨论范围之内。

We can check the dependencies of an object using SSMS. Right-click on a table and click on View Dependencies. It opens a separate window and displays the dependencies.

我们可以使用SSMS检查对象的依赖性。 右键单击表，然后单击“查看依赖项”。 它打开一个单独的窗口并显示相关性。

You can look at the dependencies and resolve it so that the procedures and views can function correctly after dropping this object.

您可以查看并解决依赖关系，以便删除该对象后，过程和视图可以正常运行。

结论 (Conclusion)

In this article, we explored the SQL DROP Table statement for removing the objects from the SQL database. You should be careful before dropping any object in the production database.

在本文中，我们探讨了SQL DROP Table语句，用于从SQL数据库中删除对象。 在删除生产数据库中的任何对象之前，您应该小心。

翻译自: https://www.sqlshack.com/an-overview-of-a-sql-drop-table-statement/