Removes an index from a table in the current database.
drop index table_name.index_name [, table_name.index_name] ...
is the table in which the indexed column is located. The table must be in the current database.
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
SQL92 – Compliance level: Transact-SQL extension.
drop index permission defaults to the index owner and is not transferable.
Commands create index
System procedures sp_cursorinfo, sp_helpindex, sp_spaceused
