drop index

Removes an index from a table in the current database.

Syntax

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

Parameters

Examples

Usage

  • 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.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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

SettingDescription
Enabled

With granular permissions enabled, you must be the table owner .

Disabled

With granular permissions disabled, you must be the table owner.

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
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

Related reference
create index
setuser