DROP TABLE (Transact-SQL)DROP TABLE (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

删除一个或多个表定义以及这些表的所有数据、索引、触发器、约束和权限规范。Removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables. 任何引用已删除表的视图或存储过程都必须使用 DROP VIEWDROP PROCEDURE 显式删除。Any view or stored procedure that references the dropped table must be explicitly dropped by using DROP VIEW or DROP PROCEDURE. 若要报告表的依赖关系,请使用 sys.dm_sql_referencing_entitiesTo report the dependencies on a table, use sys.dm_sql_referencing_entities.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
DROP TABLE [ IF EXISTS ] { database_name.schema_name.table_name | schema_name.table_name | table_name } [ ,...n ]  
[ ; ]  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
DROP TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
[;]  

参数Arguments

database_namedatabase_name
要在其中创建表的数据库的名称。Is the name of the database in which the table was created.

Azure SQL 数据库支持由三部分组成的名称格式 database_name.[schema_name].object_name,其中 database_name 为当前数据库,database_name 为 tempdb,object_name 以 # 开头。Azure SQL Database supports the three-part name format database_name.[schema_name].object_name when the database_name is the current database or the database_name is tempdb and the object_name starts with #. Azure SQL 数据库不支持由四部分组成的名称。Azure SQL Database does not support four-part names.

IF EXISTS IF EXISTS
适用范围SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)当前版本)。Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version).

有条件地删除表(仅当其已存在时)。Conditionally drops the table only if it already exists.

schema_nameschema_name
表所属架构的名称。Is the name of the schema to which the table belongs.

table_nametable_name
要删除的表的名称。Is the name of the table to be removed.

RemarksRemarks

不能使用 DROP TABLE 删除被 FOREIGN KEY 约束引用的表。DROP TABLE cannot be used to drop a table that is referenced by a FOREIGN KEY constraint. 必须先删除引用 FOREIGN KEY 约束或引用表。The referencing FOREIGN KEY constraint or the referencing table must first be dropped. 如果要在同一个 DROP TABLE 语句中删除引用表以及包含主键的表,则必须先列出引用表。If both the referencing table and the table that holds the primary key are being dropped in the same DROP TABLE statement, the referencing table must be listed first.

可以在任何数据库中删除多个表。Multiple tables can be dropped in any database. 如果一个要删除的表引用了另一个也要删除的表的主键,则必须先列出包含该外键的引用表,然后再列出包含要引用的主键的表。If a table being dropped references the primary key of another table that is also being dropped, the referencing table with the foreign key must be listed before the table holding the primary key that is being referenced.

删除表时,表的规则或默认值将被解除绑定,与该表关联的任何约束或触发器将被自动删除。When a table is dropped, rules or defaults on the table lose their binding, and any constraints or triggers associated with the table are automatically dropped. 如果要重新创建表,则必须重新绑定相应的规则和默认值,重新创建某些触发器,并添加所有必需的约束。If you re-create a table, you must rebind the appropriate rules and defaults, re-create any triggers, and add all required constraints.

如果使用 DELETE tablename 删除表中的所有行或使用 TRUNCATE TABLE 语句,则在表被删除之前,表将一直存在 。If you delete all rows in a table by using DELETE tablename or use the TRUNCATE TABLE statement, the table exists until it is dropped.

删除使用了超过 128 个区的大型表和索引时,需要分两个单独的阶段:逻辑和物理阶段。Large tables and indexes that use more than 128 extents are dropped in two separate phases: logical and physical. 在逻辑阶段中,对表使用的现有分配单元进行标记以便释放,并对其进行锁定,直到事务提交为止。In the logical phase, the existing allocation units used by the table are marked for deallocation and locked until the transaction commits. 在物理阶段,标记为要释放的 IAM 页被成批地物理删除。In the physical phase, the IAM pages marked for deallocation are physically dropped in batches.

如果删除的表包含带有 FILESTREAM 属性的 VARBINARY (MAX) 列,则不会删除在文件系统中存储的任何数据。If you drop a table that contains a VARBINARY(MAX) column with the FILESTREAM attribute, any data stored in the file system will not be removed.

重要

不应在同一个批处理中对同一个表执行 DROP TABLE 和 CREATE TABLE。DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. 否则,可能出现意外错误。Otherwise an unexpected error may occur.

权限Permissions

需要拥有该表所属架构的 ALTER 权限、该表的 CONTROL 权限或 db_ddladmin 固定数据库角色中的成员身份。Requires ALTER permission on the schema to which the table belongs, CONTROL permission on the table, or membership in the db_ddladmin fixed database role.

示例Examples

A.A. 删除当前数据库中的表Dropping a table in the current database

以下示例将从当前数据库中删除 ProductVendor1 表及其数据和索引。The following example removes the ProductVendor1 table and its data and indexes from the current database.

DROP TABLE ProductVendor1 ;  

B.B. 删除其他数据库中的表Dropping a table in another database

以下示例将删除 AdventureWorks2012AdventureWorks2012 数据库中的 SalesPerson2 表。The following example drops the SalesPerson2 table in the AdventureWorks2012AdventureWorks2012 database. 可以在服务器实例上的任何数据库中执行此示例。The example can be executed from any database on the server instance.

DROP TABLE AdventureWorks2012.dbo.SalesPerson2 ;  

C.C. 删除临时表Dropping a temporary table

以下示例将创建一个临时表,测试该表是否存在,删除该表,然后再次测试该表是否存在。The following example creates a temporary table, tests for its existence, drops it, and tests again for its existence. 此示例不使用 IF EXISTS 语法,该语法适用于 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及以上版本 。This example does not use the IF EXISTS syntax which is available beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x).

CREATE TABLE #temptable (col1 int);  
GO  
INSERT INTO #temptable  
VALUES (10);  
GO  
SELECT * FROM #temptable;  
GO  
IF OBJECT_ID(N'tempdb..#temptable', N'U') IS NOT NULL   
DROP TABLE #temptable;  
GO  
--Test the drop.  
SELECT * FROM #temptable;  
  

D.D. 使用 IF EXISTS 删除表Dropping a table using IF EXISTS

适用范围SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)当前版本)。Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version).

以下示例创建名为 T1 的表。The following example creates a table named T1. 然后,第二条语句删除表。Then the second statement drops the table. 第三条语句不执行任何操作,因为此表已删除,但这不会引起错误。The third statement performs no action because the table is already deleted, however it does not cause an error.

CREATE TABLE T1 (Col1 int);  
GO  
DROP TABLE IF EXISTS T1;  
GO  
DROP TABLE IF EXISTS T1;  

另请参阅See Also

ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
DELETE (Transact-SQL) DELETE (Transact-SQL)
sp_help (Transact-SQL) sp_help (Transact-SQL)
sp_spaceused (Transact-SQL) sp_spaceused (Transact-SQL)
TRUNCATE TABLE (Transact-SQL) TRUNCATE TABLE (Transact-SQL)
DROP VIEW (Transact-SQL) DROP VIEW (Transact-SQL)
DROP PROCEDURE (Transact-SQL) DROP PROCEDURE (Transact-SQL)
EVENTDATA (Transact-SQL) EVENTDATA (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL)sys.sql_expression_dependencies (Transact-SQL)