Viewing the Options on a Database

Use sp_helpdb to determine the options that are set for a particular database. sp_helpdb lists each active option in the “status” column of its output.

The following example shows that the read only option is turned on in mydb:

sp_helpdb mydb
name           db_size  owner  dbid   created          status
-----          -------  -----  ----   ------------     -------------------
mydb           20.0 MB    sa     5    Mar 05, 2005     read only

device_fragments      size    usage              created        free kbytes
-----------------     ------  ------------       --------       -----------
master                10.0 MB  data and log      Mar 05 2005           1792

device                         segment
------------------------------ ------------------------------
master                         default
master                         logsegment
master                         system
To display a summary of the options for all databases, use sp_helpdb without specifying a database:
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