Chapter 1: Commands

drop index

Removes an index from a table in the current database.

drop index table_name.index_name 
	[, table_name.index_name] ...

table_name: is the table in which the indexed column is located. The table must be in the current database.

index_name: is the index to drop. In Transact-SQL, index names need not be unique in a database, though they must be unique within a table.

Removes au_id_ind from the authors table:

drop index authors.au_id_ind

Once the drop index command is issued, you regain all the space that was previously occupied by the index. This space can be used for any database objects.
You cannot use drop index on system tables.
drop index cannot remove indexes that support unique constraints. To drop such indexes, drop the constraints through alter table or drop the table. See create table for more information about unique constraint indexes.
You cannot drop indexes that are currently used by any open cursor. For information about which cursors are open and what indexes they use, use sp_cursorinfo.
To get information about what indexes exist on a table, use the following, where objname is the name of the table:
```
sp_helpindex objname
```

ANSI SQL – Compliance level: Transact-SQL extension.

drop index permission defaults to the index owner and is not transferable.

Values in event and extrainfo columns of sysaudits are:

Event	Audit option	Command or access audited	Information in extrainfo
105		drop index	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – NULL Proxy information – Original login name, if a set proxy is in effect