Extracting IQ user options

[Former Member]

I'm extracting users and all the gory details regarding them from IQ.  In v15.4 there are 140 options per user stored in SYS.SYSUSEROPTIONS.  I want to extract only those options that aren't set to whatever is the default.  Any thoughts?

Update:  use SYS.SYSOPTIONS

one of those head*desk and lol at myself

'AES_Encrypt_Header_Format'

'ASE_Function_Behavior'

'Abort_On_Error_File'

'Abort_On_Error_Line'

'Abort_On_Error_Number'

'Affinity_Autoexclude_Timeout'

'Aggregate_Index_Cutover'

'Aggregation_Preference'

'Alloc_Rawdevice_Lastblock'

'Allow_Snapshot_Versioning'

'Append_Load'

'Ase_Binary_Display'

'Attention_Limit'

'BITMAP_Options1'

'BT_Prefetch_Max_Miss'

'BT_Prefetch_Size'

'BTree_Page_Split_Pad_Percent'

'Backup_Exec_Cmd'

'Backup_Free_Space'

'Bit_Vector_Pinnable_Cache_Percent'

'Bitmap_Percent_Factor'

'Blockmap_Prefetch_Size'

'Bloom_Max_Partitions'

'Bloom_Threads_Per_Partition'

'Buffer_Cache_Wash_Intensity'

'Bufman_Options1'

'Bufman_Options2'

'Bufman_Test_Options1'

'CORE_OPTIONS67'

'CORE_Options1'

'CORE_Options10'

'CORE_Options100'

'CORE_Options11'

'CORE_Options12'

'CORE_Options13'

'CORE_Options14'

'CORE_Options15'

'CORE_Options16'

'CORE_Options17'

'CORE_Options18'

'CORE_Options19'

'CORE_Options20'

'CORE_Options22'

'CORE_Options23'

'CORE_Options24'

'CORE_Options25'

'CORE_Options26'

'CORE_Options27'

'CORE_Options28'

'CORE_Options29'

'CORE_Options3'

'CORE_Options30'

'CORE_Options31'

'CORE_Options32'

'CORE_Options33'

'CORE_Options34'

'CORE_Options35'

'CORE_Options36'

'CORE_Options37'

'CORE_Options38'

'CORE_Options39'

'CORE_Options4'

'CORE_Options40'

'CORE_Options41'

'CORE_Options42'

'CORE_Options43'

'CORE_Options44'

'CORE_Options45'

'CORE_Options46'

'CORE_Options47'

'CORE_Options48'

'CORE_Options49'

'CORE_Options5'

'CORE_Options50'

'CORE_Options51'

'CORE_Options52'

'CORE_Options53'

'CORE_Options54'

'CORE_Options55'

'CORE_Options56'

'CORE_Options57'

'CORE_Options58'

'CORE_Options59'

'CORE_Options6'

'CORE_Options60'

'CORE_Options61'

'CORE_Options62'

'CORE_Options63'

'CORE_Options64'

'CORE_Options65'

'CORE_Options66'

'CORE_Options68'

'CORE_Options69'

'CORE_Options7'

'CORE_Options70'

'CORE_Options71'

'CORE_Options72'

'CORE_Options73'

'CORE_Options74'

'CORE_Options75'

'CORE_Options76'

'CORE_Options77'

'CORE_Options78'

'CORE_Options79'

'CORE_Options8'

'CORE_Options80'

'CORE_Options81'

'CORE_Options82'

'CORE_Options83'

'CORE_Options84'

'CORE_Options86'

'CORE_Options87'

'CORE_Options88'

'CORE_Options89'

'CORE_Options9'

'CORE_Options90'

'CORE_Options91'

'CORE_Options92'

'CORE_Options94'

'CORE_Options95'

'CORE_Options_Pass2_Threads'

'CREATE_HG_AND_FORCE_PHYSICAL_DELETE'

'CREATE_HG_WITH_EXACT_DISTINCTS'

'Cache_Affinity_Percent'

'Cache_Partitions'

'Check_Alias_Enabled'

'Command_Stats'

'Command_Stats_Verbosity'

'Conversion_Mode'

'Convert_Varchar_To_1242'

'Core_Options85'

'Core_Options93'

'Cursor_Window_Rows'

'DBCC_Large_Memory_Usage_Bytes'

'DBCC_Pinnable_Cache_Percent'

'DDL_Information'

'DDL_Options2'

'DDL_Options3'

'DML_Options1'

'DML_Options10'

'DML_Options101'

'DML_Options102'

'DML_Options103'

'DML_Options104'

'DML_Options105'

'DML_Options106'

'DML_Options107'

'DML_Options11'

'DML_Options12'

'DML_Options13'

'DML_Options14'

'DML_Options15'

'DML_Options16'

'DML_Options17'

'DML_Options18'

'DML_Options19'

'DML_Options2'

'DML_Options20'

'DML_Options21'

'DML_Options22'

'DML_Options23'

'DML_Options24'

'DML_Options25'

'DML_Options26'

'DML_Options27'

'DML_Options28'

'DML_Options29'

'DML_Options3'

'DML_Options30'

'DML_Options31'

'DML_Options33'

'DML_Options34'

'DML_Options35'

'DML_Options36'

'DML_Options37'

'DML_Options38'

'DML_Options39'

'DML_Options4'

'DML_Options40'

'DML_Options41'

'DML_Options42'

'DML_Options43'

'DML_Options44'

'DML_Options45'

'DML_Options46'

'DML_Options47'

'DML_Options48'

'DML_Options49'

'DML_Options5'

'DML_Options50'

'DML_Options51'

'DML_Options52'

'DML_Options53'

'DML_Options54'

'DML_Options55'

'DML_Options56'

'DML_Options57'

'DML_Options58'

'DML_Options59'

'DML_Options6'

'DML_Options60'

'DML_Options61'

'DML_Options62'

'DML_Options63'

'DML_Options64'

'DML_Options65'

'DML_Options66'

'DML_Options67'

'DML_Options68'

'DML_Options69'

'DML_Options7'

'DML_Options70'

'DML_Options71'

'DML_Options72'

'DML_Options73'

'DML_Options74'

'DML_Options75'

'DML_Options76'

'DML_Options77'

'DML_Options78'

'DML_Options79'

'DML_Options8'

'DML_Options80'

'DML_Options81'

'DML_Options82'

'DML_Options83'

'DML_Options84'

'DML_Options85'

'DML_Options86'

'DML_Options87'

'DML_Options88'

'DML_Options89'

'DML_Options9'

'DML_Options90'

'DML_Options91'

'DML_Options92'

'DML_Options93'

'DML_Options94'

'DML_Options95'

'DML_Options96'

'DML_Options97'

'DML_Options98'

'DML_Options99'

'DQP_Enabled'

'DQP_Enabled_Over_Network'

'DQP_Exclude_Types'

'DQP_Max_Fragment_KB'

'DQP_Options1'

'DQP_Options10'

'DQP_Options2'

'DQP_Options3'

'DQP_Options4'

'DQP_Options5'

'DQP_Options6'

'DQP_Options7'

'DQP_Options8'

'DQP_Options9'

'DQP_Preference'

'DQP_Row_Reduction_Percent'

'Date_First_Day_Of_Week'

'Dbcc_Log_Block_Numbers'

'Dbcc_Log_Progress'

'Default_Disk_Striping'

'Default_Having_Selectivity_PPM'

'Default_KB_Per_Stripe'

'Default_Like_Match_Selectivity_PPM'

'Default_Like_Range_Selectivity_PPM'

'Default_Proxy_Table_Row_Count'

'Default_Table_UDF_Row_Count'

'Delay_Invariants_Under_Usefulness_PPM'

'Disable_RI_Check'

'Disk_Striping_Packed'

'DmContext_Display_Limit'

'Dump_Bufman_Info_Details'

'Dump_Bufman_Info_IQMSG'

'Early_Predicate_Execution'

'Early_Resource_Release'

'Enable_Async_IO'

'Enable_LOB_Variables'

'Encrypt_Key_Per_Column_Optimizations'

'Exchange_Enabled'

'Exchange_Unit_Size'

'FHashtb_drain_pct'

'FHashtb_max_keys_per_bucket'

'FHashtb_max_rehash'

'FHashtb_rehash_pct'

'FPL_Expression_Memory_KB'

'FP_LOB_Workunit_MBSize'

'FP_Lookup_Size'

'FP_Lookup_Size_PPM'

'FP_NBIT_Autosize_Limit'

'FP_NBIT_Enforce_Limits'

'FP_NBIT_IQ15_Compatibility'

'FP_NBIT_Lookup_MB'

'FP_NBIT_Rollover_Max_MB'

'FP_Predicate_Workunit_Pages'

'FP_Prefetch_Size'

'Floating_Point_Accumulator'

'Force_Drop'

'Force_Fixed_Width_Numerics'

'Force_No_Scroll_Cursors'

'Force_Updatable_Cursors'

'Garray_Fill_Factor_Percent'

'Garray_Insert_Prefetch_Size'

'Garray_Page_Split_Pad_Percent'

'Garray_RO_Prefetch_Size'

'Give_Error_On_Control_C'

'GroupCount_selectivity_cutoff_ppm'

'Groupby_Column_Correlation'

'Groupby_Table_Correlation'

'HG_Delete_Method'

'HG_Search_Range'

'Hash_Pinnable_Cache_Percent'

'Hash_Thrashing_Percent'

'Hos_MemCheck'

'Hpux_PBO_Shutdown'

'IN_Subquery_Preference'

'IQ_Diag_Info_Level'

'IQ_Utility_Prefetch_Size'

'IQgovern_Max_priority'

'IQgovern_priority'

'IQgovern_priority_time'

'Ignore_Affinity_Cost'

'Index_Advisor'

'Index_Advisor_Max_Rows'

'Index_Preference'

'Infer_Subquery_Predicates'

'Initialize_Memory_To_Ones_On_Allocation'

'Join_Expansion_Factor'

'Join_Optimization'

'Join_Preference'

'Join_Simplification_Threshold'

'LF_Bitmap_Cache_KB'

'LF_Max_Unique_Values'

'LM_Leak_Visible'

'LOB_Prefetch_Size'

'Large_Doubles_Accumulator'

'Load_Memory_MB'

'Load_ZeroLength_AsNull'

'Log_Connect'

'Log_Cursor_Operations'

'MPX_MIPC_Timeout'

'MPX_Options1'

'MPX_Options10'

'MPX_Options11'

'MPX_Options12'

'MPX_Options2'

'MPX_Options3'

'MPX_Options4'

'MPX_Options5'

'MPX_Options6'

'MPX_Options7'

'MPX_Options8'

'MPX_Options9'

'Main_Reserved_DBSpace_MB'

'Max_Cartesian_Result'

'Max_Client_Numeric_Precision'

'Max_Client_Numeric_Scale'

'Max_Cube_Result'

'Max_GBH_Rows'

'Max_Hash_Rows'

'Max_IQ_Threads_Per_Connection'

'Max_IQ_Threads_Per_Team'

'Max_Join_Enumeration'

'Max_Partitioned_Hash_MB'

'Max_Prefix_Per_Contains_Phrase'

'Max_Query_Parallelism'

'Max_Query_Time'

'Max_Spinlock_Loop'

'Max_Temp_Space_Per_Connection'

'Max_Warnings'

'Memory_Leaks_Visible'

'Memory_Snapshot_First'

'Memory_Snapshot_Increment'

'Minimize_Storage'

'Monitor_Output_Directory'

'Mpx_Autoexclude_Timeout'

'Mpx_Heartbeat_Frequency'

'Mpx_Idle_Connection_Timeout'

'Mpx_Liveness_Timeout'

'Mpx_Max_Connection_Pool_Size'

'Mpx_Max_Global_Alloc_Size'

'Mpx_Max_Unused_Pool_Size'

'Mpx_Shtemp_Alloc_Lease_Time'

'Mpx_Suspend_MIPC_HB'

'Mpx_Suspend_Simulation'

'Mpx_Test_Options1'

'Mpx_Test_Options2'

'Mpx_Test_Options3'

'Mpx_Test_Options4'

'Mpx_Test_Options5'

'Mpx_Test_Options6'

'Mpx_Test_Options7'

'Mpx_Test_Options8'

'Mpx_Test_Options9'

'Mpx_Work_Unit_Timeout'

'Mpx_Worker_Wait_Time'

'Mpx_event'

'Mutex_Trigger_Percent'

'Mutex_Trigger_Threshold'

'Mutex_Trigger_Try_Ratio_Threshold'

'N_Emerg_Buffers'

'NoExec'

'No_Row_Reject'

'Non_Ansi_Null_Varchar'

'Notify_Modulus'

'Num_FHashtb_Buckets'

'Numeric_Overflow_Error'

'OS_File_Cache_Buffering'

'OS_File_Cache_Buffering_Tempdb'

'OS_Options2'

'OS_Options3'

'Parallel_GBH_Preference'

'Prefetch_Buffer_Limit'

'Prefetch_Buffer_Percent'

'Prefetch_FP_Percent'

'Prefetch_Garray_Percent'

'Prefetch_Hash_Percent'

'Prefetch_LOB_Percent'

'Prefetch_Sort_Percent'

'Prefetch_TextPost_Percent'

'Prefetch_Threads_Percent'

'QUERY_PLAN_HTML_DIRECTORY'

'Query_Detail'

'Query_Name'

'Query_Plan'

'Query_Plan_After_Run'

'Query_Plan_Append_Date_To_File'

'Query_Plan_As_HTML'

'Query_Plan_As_HTML_Directory'

'Query_Plan_Min_Time'

'Query_Plan_Text_Access'

'Query_Plan_Text_Caching'

'Query_Rows_Returned_Limit'

'Query_Temp_Space_Limit'

'Query_Timing'

'ROW_Prefetch_Size'

'RV_Auto_Merge_Eval_Interval'

'RV_BitMap_Chunk_Capacity'

'RV_BitMap_Chunk_Restrictions'

'RV_BitMap_RidList_Chunks_Only'

'RV_BitMap_RidList_NumRids'

'RV_DML_Options1'

'RV_Disable_Logging'

'RV_Disable_Logging_Flusher'

'RV_Fix_Data_BlockSize'

'RV_Force_ECS_DelEBM'

'RV_LM_Options_1'

'RV_Log_Options1'

'RV_MERGE_TABLE_NUMROWS'

'RV_Max_Active_SubFragment_Count'

'RV_Max_Token'

'RV_Merge_Node_MemSize'

'RV_Merge_Table_MemPercent'

'RV_Options1'

'RV_Para_Proj_WU_Size'

'RV_Reserved_DBSpace_MB'

'RV_SubFragment_Selection_Strategy'

'RV_Trace_LogBuf'

'RV_Trace_LogRec'

'RV_Trace_LogSpace'

'RV_Use_Para_Proj'

'RV_Var_Data_BlockSize'

'Revert_To_V15_Optimizer'

'Round_To_Even'

'Row_Count'

'Secondary_File_Error'

'SignificantDigitsForDoubleEquality'

'Snapshot_Versioning'

'Sort_Pinnable_Cache_Percent'

'Stats_Condition_Variables'

'Stats_Mutexes'

'Stats_RW_Locks'

'Stats_Recursive_Mutexes'

'Stats_Recursive_RW_Locks'

'Stats_Semaphores'

'Stats_Shared_Variables'

'Stats_Spinlocks'

'Stats_Threads'

'Subquery_Caching_Preference'

'Subquery_Flattening_Percent'

'Subquery_Flattening_Preference'

'Subquery_Placement_Preference'

'Sweeper_Threads_Percent'

'Table_UDF_Row_Block_Chunk_Size_KB'

'Target_Query_Parallelism'

'Temp_Extract_Append'

'Temp_Extract_Binary'

'Temp_Extract_Column_Delimiter'

'Temp_Extract_Directory'

'Temp_Extract_Escape_Quotes'

'Temp_Extract_Name1'

'Temp_Extract_Name2'

'Temp_Extract_Name3'

'Temp_Extract_Name4'

'Temp_Extract_Name5'

'Temp_Extract_Name6'

'Temp_Extract_Name7'

'Temp_Extract_Name8'

'Temp_Extract_Null_As_Empty'

'Temp_Extract_Null_As_Zero'

'Temp_Extract_Quote'

'Temp_Extract_Quotes'

'Temp_Extract_Quotes_All'

'Temp_Extract_Row_Delimiter'

'Temp_Extract_Size1'

'Temp_Extract_Size2'

'Temp_Extract_Size3'

'Temp_Extract_Size4'

'Temp_Extract_Size5'

'Temp_Extract_Size6'

'Temp_Extract_Size7'

'Temp_Extract_Size8'

'Temp_Extract_Swap'

'Temp_Reserved_DBSpace_MB'

'Test_Attention_Location'

'TextPost_Prefetch_Size'

'Text_Delete_Method'

'ThreadMgr_Silence'

'ThreadMgr_SpecialPool'

'Thread_Stacksize_KB'

'Time_Series_Error_Level'

'Time_Series_Log_Level'

'Top_NSort_CutOff_Pages'

'Trim_Partial_MBC'

'Uncorrelated_Scalar_Subquery_Selectivity_PPM'

'Unicode_Collation_Name'

'User_Resource_Reservation'

'Virtual_Backup'

'WD_Delete_Method'

'Wash_Area_Buffers_Percent'

'XT_Options1'

'XT_Options2'

'XT_Options3'

'XT_Options4'

'XT_Options5'

'allow_nulls_by_default'

'allow_read_client_file'

'allow_snapshot_isolation'

'allow_write_client_file'

'ansi_blanks'

'ansi_close_cursors_on_rollback'

'ansi_permissions'

'ansi_substring'

'ansi_update_constraints'

'ansinull'

'assume_distinct_servers'

'auditing'

'auditing_options'

'auto_commit_on_create_local_temp_index'

'background_priority'

'base_tables_in_rlv_store'

'blob_threshold'

'blocking'

'blocking_others_timeout'

'blocking_timeout'

'chained'

'checkpoint_time'

'cis_option'

'cis_rowset_size'

'close_on_endtrans'

'collect_statistics_on_dml_updates'

'compression'

'conn_auditing'

'connection_authentication'

'continue_after_raiserror'

'conversion_error'

'cooperative_commit_timeout'

'cooperative_commits'

'database_authentication'

'date_format'

'date_order'

'db_publisher'

'debug_messages'

'dedicated_task'

'default_dbspace'

'default_timestamp_increment'

'delayed_commit_timeout'

'delayed_commits'

'delete_old_logs'

'disk_sandbox'

'divide_by_zero_error'

'escape_character'

'exclude_operators'

'extended_join_syntax'

'extern_login_credentials'

'external_remote_options'

'external_udf_execution_mode'

'fire_triggers'

'first_day_of_week'

'for_xml_null_treatment'

'force_view_creation'

'global_database_id'

'http_connection_pool_basesize'

'http_connection_pool_timeout'

'http_session_timeout'

'identity_enforce_uniqueness'

'identity_insert'

'integrated_server_name'

'isolation_level'

'java_class_path'

'java_location'

'java_main_userid'

'java_vm_options'

'lock_rejected_rows'

'log_deadlocks'

'login_mode'

'login_procedure'

'materialized_view_optimization'

'max_client_statements_cached'

'max_cursor_count'

'max_hash_size'

'max_plans_cached'

'max_priority'

'max_query_tasks'

'max_recursive_iterations'

'max_statement_count'

'max_temp_space'

'min_password_length'

'min_role_admins'

'ml_remote_id'

'nearest_century'

'non_keywords'

'normalize_histogram'

'odbc_describe_binary_as_varbinary'

'odbc_distinguish_char_and_varchar'

'oem_string'

'on_charset_conversion_failure'

'on_tsql_error'

'optimization_goal'

'optimization_level'

'optimization_workload'

'pinned_cursor_percent_of_cache'

'post_login_procedure'

'precision'

'prefetch'

'preserve_source_format'

'prevent_article_pkey_update'

'priority'

'progress_messages'

'qualify_owners'

'query_mem_timeout'

'quote_all_identifiers'

'quoted_identifier'

'read_past_deleted'

'recovery_time'

'remote_idle_timeout'

'replicate_all'

'replication_error'

'replication_error_piece'

'request_timeout'

'reserved_keywords'

'return_date_time_as_string'

'rollback_on_deadlock'

'row_counts'

'save_remote_passwords'

'scale'

'secure_feature_key'

'sort_collation'

'sql_flagger_error_level'

'sql_flagger_warning_level'

'sr_date_format'

'sr_time_format'

'sr_timestamp_format'

'sr_timestamp_with_time_zone_format'

'st_geometry_asbinary_format'

'st_geometry_astext_format'

'st_geometry_asxml_format'

'st_geometry_describe_type'

'st_geometry_interpolation'

'st_geometry_on_invalid'

'string_rtruncation'

'subscribe_by_remote'

'subsume_row_locks'

'suppress_tds_debugging'

'synchronize_mirror_on_commit'

'tds_empty_string_is_null'

'temp_space_limit_check'

'time_format'

'time_zone_adjustment'

'timestamp_format'

'timestamp_with_time_zone_format'

'truncate_timestamp_values'

'trusted_certificates_file'

'tsql_outer_joins'

'tsql_variables'

'updatable_statement_isolation'

'update_statistics'

'upgrade_database_capability'

'user_estimates'

'uuid_has_hyphens'

'verify_all_columns'

'verify_password_function'

'verify_threshold'

'wait_for_commit'

'webservice_namespace_host'

'webservice_sessionid_name'

[tayeb_hadjou · Accepted solution]

Hi Jason,

The query below may help :

select A.user_name, A."option", setting from SYS.SYSUSEROPTIONS A, SYSOPTIONDEFAULTS B where lower(A."option")=lower(B.option_name) and A.setting<>B.default_value order by A.user_name, A."option"

[Former Member]

Both of those are good but I went with the following because it limits the number of options that need to be changed for users/roles/groups.  When dealing with hundreds or thousands of users per IQ box (mpx or spx) the fewer things I explicitly have to set/update is better

SELECT 'SET ' + user_name + '.' + "option" + ' = ''' + setting + ''';'

FROM sys.sysoptions

will produce the following

SET testuser.Index_Advisor = 'on';

SET testuser.Index_Advisor_Max_Rows = '100';

SET testuser.date_order = 'MDY';

SET testuser.string_rtruncation = 'OFF';

[former_member319447]

Rob V has a proc for this at sypron.nl.