Reports information about a particular segment or about all segments in the current database.
sp_helpsegment [segname]
is the name of the segment about which you want information. If you omit this parameter, information about all segments in the current database appears.
Reports information about all segments in the current database:
sp_helpsegment
segment name status ------- ------------------------------ ------ 0 system 0 1 default 1 2 logsegment 0 3 seg1 0 4 seg2 0 5 seg3 0 6 seg4 0
Reports information about the segment named order_seg. This includes database tables and indexes that bond to this segment—the tables/indexes currently having this segment specified at the table/index level—as well as the objects currently on this segment (partitions that are actually located on this segment). In addition, this example reports the total number of pages, free pages, used pages, and reserved pages on this segment:
sp_helpsegment seg1
segment name status ------- ------------------------------ ------ 3 seg1 0 device size free_pages ---------------------- -------------- ----------- pubs_dev1 2.0MB 240 Objects on segment ‘seg1’: table_name index_name indid partition_name ----------- ------------- ------ --------------- fictionsales fictionsales 0 q1 pb_fictionsales pb_fictionsales 0 lov Objects currently bound to segment ‘seg1’:
table_name index_name indid ---------- ---------- ----- new_titles new_titles 0 total_size total_pages free-pages used_pages reserved pages ---------- ----------- ---------- ---------- -------------- 2.0MB 256 240 16 0
Reports information about the default segment. The keyword default must be enclosed in quotes. The output has been abridged due to length.
sp_helpsegment "default"
segment name status ------- ------ ------ 1 default 1 device size free_pages ------ ---- ----------- master 14.0MB 303 pubs_dev1 2.0MB 240 pubs_dev2 2.0MB 232 pubs_dev3 2.0MB 232 pubs_dev4 2.0MB 240 Objects on segment ‘default’:
table_name index_name indid partition_name ---------- ---------- ----- -------------- au_pix au_pix 0 au_pix_864003078 au_pix tau_pix 0 tau_pix_864003078 ... titles title_idx 0 p1
titles title_idx 0 p2 titles title_idx 0 p3 titles title_idx 0 title_idx_985051514 Objects currently bound to segment ‘default’: table_name index_name indid ---------- ---------- ----- au_pix au_pix 0 ... titleauthor titleidind 3 titles title_idx 1 total_size total_pages free_pages used_pages reserved_pages ---------- ----------- ---------- ---------- -------------- 22.0MB 2816 1247 1569 0
Reports information about the segment on which the transaction log is stored:
1> sp_helpsegment "logsegment" 2> go
segment name status ------- ---------- ------ 2 logsegment 0 device device size ------ ------ master 14.0MB pubs_dev1 2.0MB pubs_dev2 2.0MB pubs_dev3 2.0MB pubs_dev4 2.0MB free_pages ----------- 1239 Objects on segment 'logsegment': table_name index_name indid partition_name ---------- ---------- ------ -------------- syslogs syslogs 0 syslogs_8 Objects currently bound to segment 'logsegment': table_name index_name indid ---------- ---------- ------ syslogs syslogs 0 total_size total_pages free_pages used_pages reserved_pages ------------- -------------- ------------- ------------- --------------- 22.0MB 2816 1239 13 0 (return status = 0)
sp_helpsegment displays information about the specified segment, when segname is given, or about all segments in the current database, when no argument is given.
When you first create a database, Adaptive Server automatically creates the system, default, and logsegment segments. Use sp_addsegment to add segments to the current database.
If you specify a log segment from a dedicated log database for the segname parameter, sp_helpsegment reports the number of free pages in the log segment.
The system, default, and logsegment segments are numbered 0, 1, and 2, respectively.
The “status” column indicates which segment is the default pool of space. Use sp_placeobject or the on segment_name clause of the create table or create index command to place objects on specific segments.
The “indid” column is 0 if the table does not have a clustered index and is 1 if the table has a clustered index.
Any user can execute sp_helpsegment. Permission checks do not differ based on the granular permissions settings.
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 |
|
Commands create index, create table
System procedures sp_addsegment, sp_dropsegment, sp_extendsegment, sp_helpdb, sp_helpdevice, sp_placeobject
