Checking space on segments

Tables are always copied to free space on the segment where they are currently stored, and indexes are re-created on the segment where they are currently stored. Commands that create clustered indexes can specify a segment. The copy of the table and the clustered index are created on the target segment.

To determine the number of pages available on a segment, use sp_helpsegment. The last line of sp_helpsegment shows the total number of free pages available on a segment.

The following command prints segment information for the default segment, where objects are stored when no segment was explicitly specified:

sp_helpsegment "default"

sp_helpsegment reports the names of indexes on the segment. If you do not know the segment name for a table, use sp_help and the table name. The segment names for indexes are also reported by sp_help.