sysoptions

All databases

Description

sysoptions is the new fake table queried by sp_options. When you are querying sysoptions, the names of the rows are case sensitive.

Columns

Name

Datatype

Attributes

Description

spid

int

Contains the process ID.

name

varchar(100)

Contains the name of the option.

category

varchar(100)

Contains the name of the category to which the option belongs.

currentsetting

varchar(100)

NULL

Contains the current setting of the option.

defaultsetting

varchar(100)

NULL

Contains the default setting of the option.

scope

int

Contains the bitmap used to capture information about options. The bits are ordered as follows:

  • Bit 1 – compiled time options

  • Bit 2 – stored procedure specific options

  • Bit 3 – binary options

number

int

The switch ID as an integer.

Query sysoptions using sp_options. The datatype for the current and default value is varchar so settings with varchar values can be used directly. Settings with integer values can be used after typecasting.

You do not need special privileges to query sysoptions. For example:

select * from sysoptions
where spid = 13
go

You can also use string manipulation or typecasting. For example, if an option is numeric, you can query sysoptions by entering:

if (isnumeric(currentsetting))
	select@int_val = convert(int, currentsetting)
	...
else
	select@char_val = currentsetting
	...