sysoptions

All databases

Description

sysoptions is a 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.

sysoptions shows:

Trace flags set in the runserver file with the –T options
Trace flags set with dbcc traceon(flag_number) or set switch serverwide on
Trace flags and switches set by a specific system process ID (SPID) using set switch on

sysoptions displays only the switches that are visible to the user querying the sysoptions table. That is, the user cannot see switches set privately by other SPIDs with set switch on. However, traceflags enabled using the runserver file –T option, dbcc traceon, or set switch serverwide on are visible to all users.

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