How do I query the value of a server option in Sybase?

Question

For example, if I wanted to know the current value of the quoted_identifier server option, is there a query that can give me this information?

Answer 1

sp_dboption can give you a list of settable options or actually set the option:-

List Settable Options

exec sp_dboption

set an option

sp_dboption pubs2, "unique auto_identity index", true

If you just want to list the options set on a db, you can use sp_helpdb

sp_helpdb pubs2

This Yields a multi-page resultset but the first set has a column called 'status' which shows all the 'true' options in the nominated database.

Answer 2

Some of the session-level options are available to you as a varbinary via the @@options variable. Some guidance on the interpretation of the bitmask is given by

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/41423;pt=42621

and

http://www.isug.com/Sybase_FAQ/ASE/code/sp_helpoptions.sql

For the session options which are not documented (such as quoted_identifier), you may have to resort to experimentation. I ran the following on an Solaris x86 Sybase 15 instance and saw a change in the 'third bit from the right' (counting from zero)

It is an interesting question and one that had not occured to me in my seven or so years of Sybase admin.

pjjH

262:1> set quoted_identifier off
262:2> go
263:1> select @@options
263:2> go

 --------------------------
 80210000800f0144030010

(1 row affected)
264:1> set quoted_identifier on
264:2> go
265:1> select @@options
265:2> go

 --------------------------
 80210001800f0144030018

(1 row affected)


select @@options as options into #foo

Answer 3

It is possible to querry the current velue set on selected option. It is in code of sp_helpdb procedure. You cen check sp_helptext sp_helpdb on sybsystemprocs database but it is very complicated.