DROP INDEX Statement

Removes an index from a database.

The DROP INDEX statement does not apply to indexes created by PRIMARY KEY or UNIQUE constraints. For details about dropping constraints, see the ALTER TABLE statement.

Syntax

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

where

table_name

Specifies the table where the indexed column is located. If DROP INDEX is used by the DBO or SA, then the owner name can be included to drop an index not owned by the DBO.

index_name

Specifies the index to be dropped.

Remarks

Once the DROP INDEX statement is executed, you regain all the space occupied by the index. This space can then be used for any database object.

You can't use DROP INDEX on an index on a system table.

To get information about what indexes exist on a table, use the sp_helpindex system procedure with the table name as a parameter.

Permission

DROP INDEX 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 INDEX statement. The system administrator and database owner can also use the SETUSER statement to impersonate another user.

Example

This example removes the index au_id_ind in the authors table.

DROP INDEX authors.au_id_ind

See Also