Export (0) Print
Expand All
Expand Minimize

sp_dboption

SQL Server 2000

  New Information - SQL Server 2000 SP3.

Displays or changes database options. sp_dboption should not be used on either the master or tempdb databases. sp_dboption is supported for backward compatibility. Use ALTER DATABASE to set database options.

Syntax

sp_dboption [ [ @dbname = ] 'database' ]
    [ , [ @optname = ] 'option_name' ]
    [ , [ @optvalue = ] 'value' ]

Arguments

[@dbname =] 'database'

Is the name of the database in which to set the specified option. database is sysname, with a default of NULL.

[@optname =] 'option_name'

Is the name of the option to set. It is not necessary to enter the complete option name. Microsoft® SQL Server™ recognizes any part of the name that is unique. Enclose the option name with quotation marks if it includes embedded blanks or is a keyword. If this parameter is omitted, sp_dboption lists the options that are on. option_name is varchar(35), with a default of NULL.

[@optvalue =] 'value'

Is the new setting for option_name. If this parameter is omitted, sp_dboption returns current setting. value can be true or false or on or off. value is varchar(10), with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Result Sets

If no parameters are supplied, this is the result set.

Column name Data type Description
Settable database options nvarchar(35) All of the settable database options.

If database is the only supplied parameter, this is the result set.

Column name Data type Description
The following options are set: nvarchar(35) The options that are set for the database.

If option_name is supplied, this is the result set.

Column name Data type Description
OptionName nvarchar(35) Name of the option.
CurrentSetting char(3) Whether the option is on or off.

If value is supplied, sp_dboption does not return a result set.

Remarks

These are the options set by sp_dboption. For more information about each option, see Setting Database Options.

Option Description
auto create statistics When true, any missing statistics needed by a query for optimization are automatically built during optimization. For more information, see CREATE STATISTICS.
auto update statistics When true, any out-of-date statistics needed by a query for optimization are automatically built during optimization. For  more information, see UPDATE STATISTICS.
autoclose When true, the database is shutdown cleanly and its resources are freed after the last user logs off.
autoshrink When true, the database files are candidates for automatic periodic shrinking.
ANSI null default When true, CREATE TABLE follows the SQL-92 rules to determine if a column allows null values.
ANSI nulls When true, all comparisons to a null value evaluate to UNKNOWN. When false, comparisons of non-UNICODE values to a null value evaluate to TRUE if both values are NULL.
ANSI warnings When true, errors or warnings are issued when conditions such as "divide by zero" occur.
arithabort When true, an overflow or divide-by-zero error causes the query or batch to terminate. If the error occurs in a transaction, the transaction is rolled back. When false, a warning message is displayed, but the query, batch, or transaction continues as if no error occurred.
concat null yields null When true, if either operand in a concatenation operation is NULL, the result is NULL.
cursor close on commit When true, any cursors that are open when a transaction is committed or rolled back are closed. When false, such cursors remain open when a transaction is committed. When false, rolling back a transaction closes any cursors except those defined as INSENSITIVE or STATIC.
db chaining When true, the database can be the source or target of a cross-database ownership chain. When false, the database cannot participate in cross-database ownership chaining. You can change this option for user databases, but not system databases.

The instance of SQL Server will honor this setting if the server option Cross DB Ownership Chaining (set with sp_configure) is 0 (off). If Cross DB Ownership Chaining is 1 (on), all user databases can participate in cross-database ownership chains, regardless of the value of this option.

You must be a member of the sysadmin fixed server role to change this option.

For more information, see Using Ownership Chains.

dbo use only When true, only the database owner can use the database.
default to local cursor When true, cursor declarations default to LOCAL.
merge publish When true, the database can be published for a merge replication.
numeric roundabort When true, an error is generated when loss of precision occurs in an expression. When false, losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result.
offline When true, the database is offline.
published When true, the database can be published for replication.
quoted identifier When true, double quotation marks can be used to enclose delimited identifiers.
read only When true, users can only read data in the database. Users cannot modify the data or database objects, however, the database itself can be deleted using the DROP DATABASE statement. The database cannot be in use when a new value for the read only option is specified. The master database is the exception, and only the system administrator can use master while the read only option is being set.
recursive triggers When true, enables recursive firing of triggers. When false, prevents direct recursion only. To disable indirect recursion, set the nested triggers server option to 0 using sp_configure.
select into/bulkcopy When true, the SELECT INTO statement and fast bulk copies are allowed.
single user When true, only one user at a time can access the database.
subscribed When true, the database can be subscribed for publication.
torn page detection When true, incomplete pages can be detected.
trunc. log on chkpt. When true, a checkpoint truncates the inactive part of the log when the database is in log truncate mode. This is the only option you can set for the master database.

The database owner or system administrator can set or turn off particular database options for all new databases by executing sp_dboption on the model database.

After sp_dboption has been executed, a checkpoint executes in the database for which the option was changed. This causes the change to take effect immediately.

sp_dboption changes settings for a database. Use sp_configure to change server-level settings, and the SET statement to change settings that affect only the current session.

Permissions

Execute permissions to display the list of possible database options, the list of options currently set in a database, and the current value of an option in a database (using sp_dboption with 0, 1, or 2 parameters) default to all users.

Execute permissions to change an option (using sp_dboption with all parameters) default to members of the sysadmin and dbcreator fixed server roles and the db_owner fixed database role. These permissions are not transferable.

Examples
A. Set a database to read-only

This example makes the pubs database read-only.

USE master
EXEC sp_dboption 'pubs', 'read only', 'TRUE'

Here is the result set:

The command(s) completed successfully.
B. Turn off an option

This example makes the pubs database writable again.

USE master
EXEC sp_dboption 'pubs', 'read only', 'FALSE'

Here is the result set:

The command(s) completed successfully.
C. Take a database offline

This example takes the sales database offline if there are no users accessing the database.

USE master
EXEC sp_dboption 'sales', 'offline', 'TRUE' 

Here is the result set:

The command(s) completed successfully.

See Also

ALTER DATABASE

SET

sp_configure

System Stored Procedures

Show:
© 2014 Microsoft