SQL in a Nutshell

Name

DROP INDEX

Synopsis

The DROP INDEX command destroys one or more indexes within the current database. When an index is dropped, all the space it previously consumed is immediately regained. DROP INDEX does not, however, destroy PRIMARY KEY or UNIQUE constraints, which must be done with the ALTER TABLE . . . DROP command. Refer to the CREATE TABLE command for more information about primary key and unique constraints.

Vendor	Command
SQL Server	Supported, with variations
MySQL	Supported, with variations
Oracle	Supported, with variations
PostgreSQL	Supported, with variations

SQL99 Syntax and Description

DROP INDEX table_name.index_name

PostgreSQL follows the SQL99 standard, with variations.

Microsoft SQL Server Syntax and Variations

DROP INDEX {table_name | view_name}.index_name [,...n]

Microsoft SQL Server allows indexes created on both tables and views to be dropped. If a clustered index on a table that contains nonclustered indexes is dropped, all nonclustered indexes are rebuilt and assigned new pointers.

MySQL Syntax and Variations

DROP INDEX table_name.index_name [,...n]

Older versions of MySQL include this command only for compatibility reasons; however, newer versions actually destroy the specified index. The statement is functionally equivalent to the MySQL statement ALTER TABLE . . . DROP INDEX.

MySQL allows multiple indexes to be dropped by separating each table and index name with a comma.