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 2.0 MB sa 5 Mar 05, 1999 read only device_fragments size usage free kbytes ----------------- ------ ------------ ------------- master 2.0 MB data and log 576 device segment ------------------------------ ------------------------------ master default master logsegment master system name attribute_class attribute int_value char_value comments ----- --------------- ---------- --------- -------------------- --------- pubs2 buffer manager cache name NULL cache for database mydb NULL
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 ----------- ------- ----- ---- ----------- ---------------------------
mydb 2.0 MB sa 5 May 10, 1997 read only
master 3.0 MB sa 1 Jan 01, 1997 no options set
model 2.0 MB sa 3 Jan 01, 1997 no options set
sybsystemprocs 2.0 MB sa 4 Mar 31, 1995 trunc log on chkpt tempdb 2.0 MB sa 2 May 04, 1998 select into/bulkcopy/pllsort
