sp_dropsegment System Stored Procedure

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

Syntax

sp_dropsegment segname [, logical_name]

where

segname

Is the segment to be dropped.

logical_name

Specifies the database device that you want the segment segname to no longer use. This parameter is optional, except when dropping the SYSTEM, DEFAULT, and LOGSEGMENT system segments from a database device.

Remarks

You cannot drop a segment if it is being referenced by any table or index in the current database.

If you do not supply a logical_name, the segment is dropped from the current database. If you supply a logical_name, the segment is dropped only from the named database device; it remains mapped to any previously specified devices and the segment remains in the database.

Using the sp_placeobject system stored procedure to change future space allocations for a table or an index from one segment to another removes the reference to the original segment. You can drop the original segment name with sp_dropsegment.

For the SYSTEM, DEFAULT, and LOGSEGMENT system segments, specify the device name from which you want the segments dropped.

Examples

A.    Drop a Segment

This example drops the segment INDEXES from the current database.

sp_dropsegment INDEXES

B.    Unmap a Segment

This example unmaps the segment INDEXES from the database device DEV1.

sp_dropsegment INDEXES, DEV1

Permission

Execute permission defaults to the database owner.

Tables Used

sysindexes, syssegments, master.dbo.sysdevices, master.dbo.sysusages

See Also