SQLDROPTABLE语句概述
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, , 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 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.
您收到以下错误信息。
sql中delete用法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.

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。