sp_options

Shows option values.

Syntax

sp_options [ [show | help
	[, option_name | category_name | null
	[, dflt | non_dflt | null [, spid] ] ] ] ]

Parameters

Examples

Usage

Use sp_options to view settings for the following options:

  • set plan dump / load

  • set plan exists check

  • set forceplan

  • set plan optgoal

  • set [optCriteria]

  • set plan opttimeoutlimit

  • set plan replace

  • set statistics simulate

  • set metrics_capture

  • set prefetch

  • set parallel_degree number

  • set process_limit_action

  • set resource_granularity number

  • set scan_parallel_degree number

  • set repartition_degree number

Permissions

Any user can execute sp_options. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect