DROP TABLE

Transact-SQL Reference

Transact-SQL Reference

DROP TABLE

Removes a table definition and all data, indexes, triggers, constraints, and permission specifications for that table. Any view or stored procedure that references the dropped table must be explicitly dropped by using the DROP VIEW or DROP PROCEDURE statement.

Syntax

DROP TABLE table_name

Arguments

table_name

Is the name of the table to be removed.

Remarks

DROP TABLE cannot be used to drop a table referenced by a FOREIGN KEY constraint. The referencing FOREIGN KEY constraint or the referencing table must first be dropped.

A table owner can drop a table in any database. When a table is dropped, rules or defaults on it lose their binding, and any constraints or triggers associated with it are automatically dropped. If you re-create a table, you must rebind the appropriate rules and defaults, re-create any triggers, and add all necessary constraints.

You cannot use the DROP TABLE statement on system tables.

If you delete all rows in a table (DELETE tablename) or use the TRUNCATE TABLE statement, the table exists until it is dropped.

Permissions

DROP TABLE permissions default to the table owner, and are not transferable. However, members of the sysadmin fixed server role or the db_owner and db_dlladmin fixed database roles can drop any object by specifying the owner in the DROP TABLE statement.

Examples
A. Drop a table in the current database

This example removes the titles1 table and its data and indexes from the current database.

DROP TABLE titles1
B. Drop a table in another database

This example drops the authors2 table in the pubs database. It can be executed from any database.

DROP TABLE pubs.dbo.authors2

See Also

ALTER TABLE

CREATE TABLE

DELETE

sp_depends

sp_help

sp_spaceused

TRUNCATE TABLE