Using sp_helpdb to Find Database Device Names and Options

Use sp_helpdb with the database name to find the names of the database devices on which a particular database resides.

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
pubs_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 System Administration Guide, Volume 1 > 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.

When you use sp_helpdb without arguments, it reports information about all databases in SAP ASE:

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