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
Reports information about the segment named order_seg, including which database tables and indexes use that segment and the total number of pages, free pages and used pages on the segment:
sp_helpsegment order_seg
segment name status ------- ------------------------------ ------ 3 order_seg 0 device size free_pages ---------------------- -------------- ----------- tpcd_data1 25.0MB 8176 tpcd_data2 25.0MB 8512 tpcd_data3 25.0MB 8392 tpcd_data4 25.0MB 8272 tpcd_data5 25.0MB 8448 tpcd_data6 25.0MB 8512 table_name index_name indid ---------------------- ---------------------- ------ orders orders 0 total_size total_pages free_pages used_pages -------------- ----------- ----------- ----------- 150.0MB 76800 50312 26488
Reports information about the default segment. The keyword default must be enclosed in quotes:
sp_helpsegment "default"
Reports information about the segment on which the transaction log is stored:
sp_helpsegment logsegment
segment name status ------- ------------------------------ ------ 2 logsegment 0 device size free_pages ---------------------- ----------------- ----------- tpcd_log1 20.0MB 10200 table_name index_name indid ---------------------- ------------------------- ------ syslogs syslogs 0 total_size total_pages free_pages used_pages ----------------- ----------- ----------- ----------- 20.0MB 10240 10200 40
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.
Commands create index, create table
System procedures sp_addsegment, sp_dropsegment, sp_extendsegment, sp_helpdb, sp_helpdevice, sp_placeobject
