sp_show_options

Description

Prints all the server options that have been set in the current session.

Syntax

sp_show_options

Parameters

Examples

Example 1

This example 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.