sp_show_options

Syntax

sp_show_options

Examples

• Example 1 – Displays the output from sp_show_options:

  create procedure sp_show_options
  as
  select a.number, a.name
          from master.dbo.spt_values a, master.dbo.spt_values c
          where   c.type = "P"
          and a.type='N'
          and c.low <= datalength(@@options)
          and a.number = c.number
          and convert(tinyint,substring(@@options, c.low, 1)) & c.high != 0
      return (0)
  go

  1> sp_show_options
  2> go

   number      name
   ----------- ----------------------------
              7 arithabort
              8 numeric_truncation
             13 control
             40 prefetch
             41 triggers
             42 replication
             43 replication force_dll
             48 transactional_rpc
             58 remote_indexes
             62 statement_cache
             64 proc_return_status
             65 proc_output_params
  
  (12 rows affected)
  (return status = 0)

Usage

@@options the array of bits corresponding to server options. For every option, “low” is the byte number in @@options, and “high” is the bit within that byte corresponding to the option. If the bit is set, print name of that option.

Permissions

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