Use sp_help_set_options to get an understandable listing of the current state of one or more of the current SET options.
sp_help_set_options [@SetOptNamePattern = {optname}]
[, @Action = action]
[, @ResultBits = @ScriptVar OUTPUT]
where:
optname
Is the name of the SET option to be reported. If optname is given as a Transact-SQL wildcard pattern, all options whose name matches the pattern will be reported.
Action
Is either 'display_all' or 'display_rows'. The default is 'display_rows'.
@ResultBits = @ScriptVar OUTPUT
This parameter gives a summary code of the status of the SET options found. @ScriptVar should be defined as an integer.
If sp_help_set_options is executed with no optname, it will report the current state of all the SET options. If sp_help_set_options is executed with an optname parameter containing the name of one of the SET options, it will report the state of that option. If sp_help_set_options is executed with an optname parameter containing a LIKE wildcard pattern, it will report the state of any option whose name matches the pattern.
If sp_help_set_options is executed with an action parameter of 'display_rows' it will return only the result set indicating the state of the SET options report on in that execution. If the action parameter is set to 'display_all' it will also report a summary code indicating how many options were matched. The summary code, @ResultBits, is a bitmap whose bits are set as follows:
0x01 - optname/optpattern matched at least one row.
0x02 - optname/optpattern matched exactly one row.
0x04 - at least one matched option is ON.
0x08 - at least one matched option is different than its default.
The result set from sp_help_set_options has three columns:
This reports 'same' if the option's state matches that of the current default setting for the server and 'different' if the option's state does not match the default. The default settings for a server are controlled by the SA using the sp_configure command with the 'user options' parameter.
This is the name of the option to which the row pertains.
This is the state (ON or OFF) of the option for the current connection.
To report all of the current option settings:
sp_help_set_options
To report the settings of all options whose name contains the string ANSI:
sp_help '%ANSI%'
To report the setting for the NOCOUNT option with the @ResultBit summary code:
sp_help NOCOUNT, 'display_all'
To report the settings for all options with the @ResultBit summary code:
declare @Summary int
declare @Msg char(50)
exec sp_help_set_options @Action = 'display_all',
@ResultBits = @Summary OUTPUT
select @Msg = 'Result Summary = ' + convert(char(2), @Summary)
print @Msg