sp_helpsegment

Description

Reports information about a particular segment or about all segments in the current database.

Syntax

sp_helpsegment [segname]

Parameters

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.

Examples

Example 1

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

Example 2

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

Example 3

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

Example 4

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)

Usage

Permissions

Any user can execute sp_helpsegment.

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 create index, create table

System procedures sp_addsegment, sp_dropsegment, sp_extendsegment, sp_helpdb, sp_helpdevice, sp_placeobject