Removes a table definition and all data, indexes, triggers, constraints, and permission specifications for that table from the database.
DROP TABLE [[database.]owner.]table_name
[, [[database.]owner.]table_name...]
where
The DROP TABLE statement cannot be used to drop a table that is being referenced by a FOREIGN KEY constraint. You must first drop the referencing FOREIGN KEY constraint or the referencing table.
If you are the table owner, you can drop a table in any database. When you drop a table, 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.
The sysobjects, syscolumns, sysindexes, sysprotects, and syscomments system tables are affected when a table is dropped.
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 still exists until you drop it.
DROP TABLE permission defaults to the table owner and is not transferable. However, the database owner and system administrator can drop any object by specifying the owner in the DROP TABLE statement. The system administrator and database owner can also use the SETUSER statement to impersonate another user.
This example removes the titles1 table and its data and indexes from the current database.
DROP TABLE titles1
The following example drops the authors2 table in the pubs database. This can be executed from any database.
DROP TABLE pubs.dbo.authors2
| ALTER TABLE | sp_help |
| CREATE TABLE | sp_spaceused |
| DELETE | TRUNCATE TABLE |
| sp_depends |