For the connected user, displays a list of the current value and the default value of database and server startup options that have been changed from the default.
sp_iqcheckoptions
Requires no parameters. Returns one row for each option that has been changed from the default value. The output is sorted by option name, then by user name.
None. The DBA sees all options set on a permanent basis for all groups and users and sees temporary options set for DBA. Non-DBA users see their own temporary options. All users see non-default server startup options.
For the connected user, the sp_iqcheckoptions stored procedure displays a list of the current value and the default value of database and server startup options that have been changed from the default. sp_iqcheckoptions considers all IQ and ASA database options. IQ modifies some ASA option defaults, and these modified values become the new default values. Unless the new IQ default value is changed again, sp_iqcheckoptions does not list the option.
When sp_iqcheckoptions is run, the DBA sees all options set on a permanent basis for all groups and users and sees temporary options set for DBA. Non-DBA users see their own temporary options. All users see non-default server startup options.
Column name |
Description |
|---|---|
User_name |
The name of the user or group for whom the option has been set. At database creation, all options are set for the PUBLIC group. Any option that has been set for a group or user other than PUBLIC is displayed. |
Option_name |
The name of the option |
Current_value |
The current value of the option |
Default_value |
The default value of the option |
Option_type |
“Temporary” for a TEMPORARY option, else “Permanent” |
In these examples, the temporary option Append_Load is set to ON and the group mygroup has the option Max_Warnings set to 9. The user joel has a temporary value of 55 set for Max_Warnings.
In the first example, sp_iqcheckoptions is run by the DBA.
User_name Option_name Current_value Default_value Option_type DBA Ansi_update_constr CURSORS Off Permanent PUBLIC Ansi_update_constr Cursors Off Permanent DBA Append_Load ON OFF Temporary DBA Checkpoint_time 20 60 Temporary DBA Connection_authent Company=MyComp; Temporary Application=DBTools;Signa DBA Login_procedure DBA.sp_iq_proce sp_login_envir Permanent PUBLIC Login_procedure DBA.sp_iq_proce sp_login_envir Permanent mygroup Max_Warnings 9 281474976710655 Permanent DBA Min_NLPDJ_Table_Size 1000000 10000 Permanent PUBLIC Min_NLPDJ_Table_Size 1000000 10000 Permanent DBA Thread_count 25 0 Temporary
In the second example, sp_iqcheckoptions is run by the user joel.
User_name Option_name Current_value Default_value Option_type joel Ansi_update_constr CURSORS Off Permanent PUBLIC Ansi_update_constr Cursors Off Permanent joel Checkpoint_time 20 60 Temporary joel Connection_authent Company=MyComp; Temporary Application=DBTools;Signa joel Login_procedure DBA.sp_iq_proce sp_login_envir Permanent PUBLIC Login_procedure DBA.sp_iq_proce sp_login_envir Permanent joel Max_Warnings 55 281474976710655 Temporary joel Min_NLPDJ_Table_Size 1000000 10000 Permanent PUBLIC Min_NLPDJ_Table_Size 1000000 10000 Permanent joel Thread_count 25 0 Temporary
