User Tools

Site Tools



Show settable database options:

EXEC sp_dboption 

Settable database options:

  • ANSI null default
  • ANSI nulls
  • ANSI padding
  • ANSI warnings
  • arithabort
  • auto create statistics
  • auto update statistics
  • autoclose
  • autoshrink
  • concat null yields null
  • cursor close on commit
  • db chaining
  • dbo use only
  • default to local cursor
  • merge publish
  • numeric roundabort
  • offline
  • published
  • quoted identifier
  • read only
  • recursive triggers
  • select into/bulkcopy
  • single user
  • subscribed
  • torn page detection
  • trunc. log on chkpt.

Show database options that are set:

EXEC sp_dboption 'MyDatabase'
The following options are SET
auto CREATE statistics
auto UPDATE statistics

Show the current setting of an option:

EXEC sp_dboption 'MyDatabase', 'autoshrink'
OptionName     CurrentSetting
-------------- --------------
autoshrink     OFF

Change the current setting of an option:

EXEC sp_dboption 'MyDatabase', 'autoshrink', TRUE

More examples of changing the option settings:

EXEC sp_dboption 'MyDatabase', 'auto create statistics', TRUE
EXEC sp_dboption 'MyDatabase', 'auto update statistics', TRUE
notes/sql/sp_dboption.txt · Last modified: 2020/08/26 (external edit)