SQL Server 6.5 introduces the @@options global variable, which records the
current state of a number of user options. While you can select @@options
to determine the current settings, it only returns an integer, which can be
difficult to interpret. This article describes how to create a stored
procedure you can run for a more meaningful display of @@options.
Run the following script as the system administrator (SA) with either ISQL
or ISQL/w:
use master
go
if (exists (select * from sysobjects
where name = 'sp_currentopts'))
drop procedure sp_currentopts
go
if (exists (select * from sysobjects
where name = 'sysuseropts'))
drop table sysuseropts
go
create table sysuseropts
(optid int NOT NULL,
options_set varchar(25) NOT NULL)
go
insert into sysuseropts values (0,'NO OPTIONS SET')
insert into sysuseropts values (1,'DISABLE_DEF_CNST_CHK')
insert into sysuseropts values (2,'IMPLICIT_TRANSACTIONS')
insert into sysuseropts values (4,'CURSOR_CLOSE_ON_COMMIT')
insert into sysuseropts values (8,'ANSI_WARNINGS')
insert into sysuseropts values (16,'ANSI_PADDING')
insert into sysuseropts values (32,'ANSI_NULLS')
insert into sysuseropts values (64,'ARITHABORT')
insert into sysuseropts values (128,'ARITHIGNORE')
insert into sysuseropts values (256,'QUOTED_IDENTIFIER')
insert into sysuseropts values (512,'NOCOUNT')
insert into sysuseropts values (1024,'ANSI_NULL_DFLT_ON')
insert into sysuseropts values (2048,'ANSI_NULL_DFLT_OFF')
go
grant select on sysuseropts to public
go
create procedure sp_currentopts as
if @@options <> 0
select options_set
from master.dbo.sysuseropts
where (optid & @@options) > 0
else
select options_set
from master.dbo.sysuseropts
where optid = 0
go
grant execute on sp_currentopts to public
go
If you then run
sp_currentopts, you will get a result set listing the current user options set for your connection. For example, suppose a
database administrator (DBA) runs the following commands:
sp_configure 'user options', 1400
go
reconfigure
go
A user who then logged on and ran
sp_currentopts would receive the
following:
options_set
-------------------------
ANSI_WARNINGS
ANSI_PADDING
ANSI_NULLS
ARITHABORT
QUOTED_IDENTIFIER
ANSI_NULL_DFLT_ON
Likewise, if a user logs on to a system where the
sp_configure 'user options' setting is set to 0 (zero) and then issues a SET ANSI_WARNINGS ON command,
sp_currentopts would return the following:
options_set
-------------------------
ANSI_WARNINGS
Back to the top
