Finding option settings

You can obtain a list of option settings, or the values of individual options, in a variety of ways.

Getting a list of option values

Current option settings for your connection are available as a subset of connection properties. You can list all connection properties using the sa_conn_properties system procedure.

CALL sa_conn_properties;

To order this list alphabetically, you can execute the following statement:

SELECT * 
FROM sa_conn_properties( )
ORDER BY PropName;

If you want to filter the result or order by anything other than name, you could also use a WHERE clause. For example:

SELECT * 
FROM sa_conn_properties( )
WHERE PropDescription LIKE '%cache%'
ORDER BY PropNum;

See sa_conn_properties system procedure.

In Interactive SQL, the SET statement with no arguments lists the current setting of options.
SET;
In Sybase Central, select a database, and then choose File » Options.
Use the following query on the SYSOPTIONS system view to display all PUBLIC values, and those USER values that have been explicitly set:
SELECT * FROM SYSOPTIONS;

Getting individual option values

You can obtain a single setting using the CONNECTION_PROPERTY system function. For example, the following statement reports the value of the ansi_blanks option:

SELECT CONNECTION_PROPERTY ( 'ansi_blanks' );

See CONNECTION_PROPERTY function [System].

Monitoring option settings

You can use the sa_server_option system procedure to instruct the database server to send a message or return an error when an attempt is made to set a database option.

You use the OptionWatchList property to create a list the options that you want to monitor, and the OptionWatchAction property to specify the action the database server should take when an attempt is made to set an option that is being monitored.

For example, the following command instructs the database server to monitor the database options automatic_timestamp, float_as_double, and tsql_hex_constant:

CALL dbo.sa_server_option( 
'OptionWatchList','automatic_timestamp,float_as_double,tsql_hex_constant' );

The following command instructs the database server to return an error if an attempt is made to set an option specified in the OptionWatchList property:

CALL dbo.sa_server_option( 'OptionWatchAction','ERROR' );