sp_addsegment

Description

Defines a segment on a database device in a database.

Syntax

sp_addsegment segname, dbname, devname

Parameters

segname

is the name of the new segment to add to the syssegments table of the database. Segment names are unique in each database.

dbname

specifies the name of the database in which to define the segment. dbname must be the name of the current database or match the database name qualifying sp_addsegment.

devname

is the name of the database device in which to locate segname. A database device can have more than one segment associated with it.

Examples

Example 1

Creates a segment named indexes for the database pubs2 on the database device named dev1:

sp_addsegment indexes, pubs2, dev1

Example 2

Creates a segment named indexes for the pubs2 database on the database device named pubs2_dev:

disk init 
    name = "pubs2_dev", 
    physname = "/dev/pubs_2_dev",
    vdevno = 9, size = 5120
go
alter database pubs2 on pubs2_dev = 2
go
pubs2..sp_addsegment indexes, pubs2, dev1

Usage

Permissions

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

Granular permissions enabled

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

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

Commands alter database, create index, create table, disk init

System procedures sp_dropsegment, sp_extendsegment, sp_helpdb, sp_helpdevice, sp_placeobject