sp_dropsegment

Description

Drops a segment from a database or unmaps a segment from a particular database device.

Syntax

sp_dropsegment segname, dbname [, device]

Parameters

segname

is the name of the segment to be dropped.

dbname

is the name of the database from which the segment is to be dropped.

device

is the name of the database device from which the segment segname is to be dropped. This parameter is optional, except when the system segment system, default, or logsegment is being dropped from a database device.

Examples

Example 1

This command drops the segment indexes from the pubs2 database.

sp_dropsegment indexes, pubs2

Example 2

This command unmaps the segment indexes from the database device dev1:

sp_dropsegment indexes, pubs2, dev1

Usage

Permissions

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

Granular permissions enabled

With granular permissions enabled, you must be a user with manage database privilege.

Granular permissions disabled

With granular permissions disabled, you must be the database owner or a user with sa_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

System procedures sp_addsegment, sp_addthreshold, sp_helpsegment, sp_helpthreshold, sp_placeobject