Displaying Information About Segments

You can view detailed information about segments by using sp_helpsegment and sp_helpdb.

The sp_helpsegment system stored procedure reports information about a particular segment or about all of the segments in the current database.

To display information about segments

sp_helpsegment [segname]

where

segname: Specifies the segment about which you want information. If you omit this parameter, information about all segments in the current database is displayed. If the segname is DEFAULT, you must enclose it in quotation marks (because DEFAULT is a reserved word).

If you run the sp_helpsegment system stored procedure without a segname, it displays the number, name, and status for all segments in the database. The status is 1 for the default segment (the segment that is used if ON segment_name is not used in a CREATE statement). For example:

sp_helpsegment


segment name                      status
------- ------------------------- --------- 
     0  system                    0      
     1  default                   1      
     2  logsegment                0      
     3  seg1                      0      
     4  seg2                      0

If you run the sp_helpsegment system stored procedure with a segname, it displays the number, name, and status of that segment and also displays the names of the database devices and their sizes, plus information about the tables and indexes on the segment. For example, to display information about the seg1 segment, type:

sp_helpsegment seg1


segment name                      status 
------- ------------------------- --------- 
3       seg1                      0


device                        size
----------------------------- -----------------   
seg_mydisk1                   2MB


table_name             index_name   indid   
---------------------- --------     --------   
authors                au_ind       1

The sp_helpdb system procedure displays information about the relationship between database devices and segments within a particular database.

To display information about the segments in a database

sp_helpdb [dbname]

where

dbname: Specifies the database you want information about. Omitting this optional parameter results in a report on all databases.

For example, to display information about the segments in mydata, type:

sp_helpdb mydata

name	db_size	owner	dbid	created	status
-----	---------	-----	-------	---------	-------
mydata	6 MB	sa	4	May 5 1995	no options set

device_fragments	size	usage
------------------	-------------	----------
bigdevice	4 MB	data only
logdev	2 MB	logonly

device         segment
-------------  --------
bigdevice      default   
bigdevice      system   
logdev         logsegment

When the dbname parameter is omitted, information about all databases on the server is returned, but the displayed information does not include the device_fragments, size, and usage columns.

To display information about all databases on the server, type:

sp_helpdb

For more information about sp_helpsegment and sp_helpdb, see the Microsoft SQL Server Transact-SQL Reference.