drop table

Description

Removes a table definition and all of its data, indexes, partition properties, triggers, encryption properties, and permissions from the database.

Syntax

drop table [[database.]owner.]table_name 
	[, [[database.]owner.]table_name] ...

Parameters

table_name

is the name of the table to drop. Specify the database name if the table is in another database, and specify the owner’s name if more than one table by the same name exists in the database. The default value for owner is the current user, and the default value for database is the current database.

Examples

Example 1

Removes the table roysched and its data and indexes from the current database:

drop table roysched

Usage


Restrictions


Dropping tables with cross-database referential integrity constraints

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for drop table differ based on your granular permissions settings.

Granular permissions enabled

With granular permissions enabled, you must be the table owner or a user with drop any table privilege.

Granular permissions disabled

With granular permissions disabled, you must be the table owner or a user with sa_role.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

27

drop

drop table

  • Roles – current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

See also

Commands alter table, create table, delete, truncate table

System procedures sp_depends, sp_help, sp_spaceused