drop index

Description

Removes an index from a table in the current database.

Syntax

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

Parameters

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.

Examples

Example 1

Removes au_id_ind from the authors table:

drop index authors.au_id_ind 

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

drop index permission defaults to the index owner and is not transferable. However, database owners can drop the index using setuser.

Auditing

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

See also

Commands create index, setuser

System procedures sp_cursorinfo, sp_helpindex, sp_spaceused