drop index

Syntax

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.

Example 1 – 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
```

See also sp_cursorinfo, sp_helpindex, sp_spaceused in Reference Manual: Procedures.

ANSI SQL – Compliance level: Transact-SQL extension.

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

Setting	Description
Enabled	With granular permissions enabled, you must be the table owner .
Disabled	With granular permissions disabled, you must be the table owner.

Values in event and extrainfo columns of sysaudits are:

Information	Values
Event	105
Audit option
Command or access audited	drop index
Information in `extrainfo`	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