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
	...