sp_dboption

Description

Displays or changes database options.

Syntax

sp_dboption [dbname, optname, {true | false}]

Parameters

dbname

is the name of the database in which the option is to be set. You must be using master to execute sp_dboption with parameters (that is, to change a database option). You cannot, however, change option settings in the master database.

optname

is the name of the option to be set. Adaptive Server understands any unique string that is part of the option name. Use quotes around the option name if it is a keyword or includes embedded blanks or punctuation.

true | false

true to turn the option on, false to turn it off.

Examples

Example 1

Displays a list of the database options:

sp_dboption

Settable database options

 database_options
 ------------------------
 abort tran on log full 
 allow nulls by default 
 auto identity
 dbo use only 
 ddl in tran 
 identity in nonunique index
 no chkpt on recovery 
 no free space acctg 
 read only 
 select into/bulkcopy/pllsort 
 single user 
 trunc log on chkpt
 trunc. log on chkpt. 
 unique auto_identity index

Example 2

Makes the database pubs2 read only. The read string uniquely identifies the read only option from among all available database options. Note the use of quotes around the keyword read:

use pubs2 
go 
master..sp_dboption pubs2, "read", true 
go 
checkpoint
go

Example 3

Makes the database pubs2 writable again:

pubs2..sp_dboption pubs2, "read", false 
go 
checkpoint 
go

Example 4

Allows select into, bcp and parallel sort operations on tables in the pubs2 database. The select into string uniquely identifies the select into/ bulkcopy option from among all available database options:

use pubs2
go
master..sp_dboption pubs2, "select into", true
go 
checkpoint
go

NoteQuotes are required around the option because of the embedded space.

Example 5

Automatically defines 10-digit IDENTITY columns in new tables created in mydb. The IDENTITY column, SYB_IDENTITY_COL, is defined in each new table that is created without specifying either a primary key, a unique constraint, or an IDENTITY column:

use mydb 
go
master..sp_dboption mydb, "auto identity", true
go
checkpoint
go

Example 6

Automatically includes an IDENTITY column in the mydb tables’ index keys, provided these tables already have an IDENTITY column. All indexes created on the tables will be internally unique:

use master 
go 
sp_dboption mydb, "identity in nonunique index", true 
go 
use mydb 
go 
checkpoint
go

Example 7

Automatically includes an IDENTITY column with a unique, nonclustered index for new tables in the pubs2 database:

use master 
go 
sp_dboption pubs2, "unique auto_identity index", true 
go 
use pubs2 
go 
checkpoint
go

Usage


Database options

Permissions

Only a System Administrator or the Database Owner can execute sp_dboption with parameters to change database options. A user aliased to the Database Owner cannot execute sp_dboption to change database options. Any user can execute sp_dboption with no parameters to view database options.

See also

Commands checkpoint, select

System procedures sp_configure, sp_helpdb, sp_helpindex, sp_helpjoins

Utilities bcp