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

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) 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.

當 database_name 是目前的資料庫或 database_name 是 tempdb,而且 object_name 開頭為 # 時,Azure SQL Database 支援三部分名稱格式 database_name.[schema_name].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 Database 不支援四部分名稱。Azure SQL Database does not support four-part names.

IF EXISTSIF EXISTS
適用於SQL ServerSQL Server (SQL 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 table_name 或 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 Server (SQL 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)