sp_helpdb

When you execute sp_helpdb within a database, and give that database’s name, you see information about the segments in the database.

For example:

sp_helpdb pubs2
name      db_size    owner     dbid created        status
--------- ---------- --------- ---- -------------- --------------
pubs2     20.0 MB     sa           4 Apr 25, 2005   select
     into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data

device_fragments    size          usage         created      free kbytes
------------------- ------------- ------------- ----------   ------------
master              10.0MB        data and log  Apr 13 2005          1792
pubs_2_dev         10.0MB        data and log  Apr 13 2005         9888

device                 segment
---------------------- ----------------------
master                 default
master                 logsegment
master                 system
pubs­_2_dev             default
pubs­_2_dev             logsegment
pub­s_2_dev             system
pubs­_2_dev             seg1
pubs­_2_dev             seg2