use sybsystemprocs
go

if object_id('sp_helpoptions') is not null
begin
    drop procedure sp_helpoptions
    if object_id('sp_helpoptions') is not null
        print '<<< Failed to drop procedure sp_helpoptions >>>'
    else
        print '<<< Dropped procedure sp_helpoptions >>>'
end
go



create procedure sp_helpoptions as

-- initial design by Bret Halford (bret@sybase.com) 10 Jan 2000
-- with assistance from Kimberly Russell
-- relies only on @@options, developed on ASE 11.5.x Solaris

-- This stored procedure displays a list of SET options and indicates
-- for each option if the option is ON or OFF

-- The @@options global variable contains bits that indicate
-- whether certain of the SET command options are on or not.

-- By observing the difference (if any) in @@options value when an
-- option is on and off, a test can be derived for that condition

-- Note that @@options is not documented in the manuals and its details
-- are possibly subject to change without notice and may vary by platform.

-- This procedure can probably be expanded to test for other SET command
-- options as well.  If you come up with a test for any other SET option,
-- please send it to me and I will add it to the procedure.

declare @high_bits int
declare @low_bits int
select @high_bits = convert(int,substring(@@options,1,4))
select @low_bits = convert(int,substring(@@options,5,4))

if (@high_bits & 268435456 = 268435456 )    print "showplan is on"
else print "showplan is off"

if (@low_bits & 33554432 = 33554432) print "ansinull is on"
else print "ansinull is off"

if (@low_bits & 536870912 = 536870912) print "ansi_permissions is on"
else print "ansi_permissions is off"

if (@high_bits & -2147418112 = -2147418112) print "arithabort is on"
else print "arithabort is off"

if (@high_bits & 1073741824 = 1073741824) print "arithignore is on"
else print "arithignore is off"

if (@high_bits & 1073741824 = 1073741824) print "arithignore arith_overflow"
else print "arithignore arith_overflow off"

if (@high_bits & 32 = 32) print "close on endtran is on"
else print "close on endtran is off"

if (@high_bits & 32768 = 32768) print "nocount is on"
else print "nocount is off"

-- Note: if 'noexec' or 'parseonly' were on, this procedure could not run,
-- so no test is necessary.
print 'noexec is off'
print 'parseonly is off.'

go

if object_id('sp_helpoptions') is not null
begin
    print '<<< Created procedure sp_helpoptions >>>'
    grant execute on sp_helpoptions to public
end
else
    print '<<< Failed to create procedure sp_helpoptions >>>'
go

