Database device names and options

To find the names of the database devices on which a particular database resides, use sp_helpdb with the database name:

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

sp_helpdb reports on the size and usage of the devices used by the named database. The status column lists the database options. These options are described in Chapter 8, “Setting Database Options.”

If you are using the named database, sp_helpdb also reports on the segments in the database and the devices named by the segments. See Chapter 8, “Creating and Using Segments,” for more information.

When you use sp_helpdb without arguments, it reports information about all databases in Adaptive Server:

sp_helpdb 
name            db_size  owner dbid   created            status
-------------   -------- ----- ----   ------------       -------------------
master          48.0 MB   sa      1   Apr 12, 2005       mixed log and data
model            8.0 MB   sa      3   Apr 12, 2005       mixed log and data
pubs2           20.0 MB   sa      6   Apr 12, 2005       select into/
     bulkcopy/pllsort, trunc log on chkpt, mixed log and data
sybsystemdb      8.0 MB   sa      5   Apr 12, 2005       mixed log and data
sybsystemprocs 112.0 MB   sa      4   Apr 12, 2005       trunc log on chkpt,
     mixed log and data
tempdb           8.0 MB   sa      2   Apr 12, 2005       select into/
     bulkcopy/pllsort, trunc log on chkpt, mixed log and data