sp_dropsegment

Syntax

sp_dropsegment segname, dbname [, device]

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.

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
```

There are additional considerations when using sp_dropsegment:

You can drop a segment if it is not referenced by any table, index, or partition in the specified database.
If you:
- Do not supply device – the segment is dropped from the specified database.
- Supply device – the segment is no longer mapped to the named database device, but the segment is not dropped.
Dropping a segment drops all thresholds associated with that segment.
You can only execute sp_dropsegment for the logsegment system segment in single-user mode.

Note: This command may take a long time to complete in in very large databases.
When you unmap a segment from one or more devices, the SAP ASE server drops any thresholds that exceed the total space on the segment. When you unmap the logsegment from one or more devices, the SAP ASE server recalculates the last-chance threshold.
sp_placeobject changes future space allocations for a table or index from one segment to another, and removes the references from the original segment. After using sp_placeobject, you can drop the original segment name with sp_dropsegment.
For the system segments system, default, and logsegment, you must specify the device name from which you want the segments dropped.

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

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage database` privilege.
Disabled	With granular permissions disabled, you must be the database owner or a user with sa_role.

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

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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