How To Display Current Session SET Options? [ SQL Server ]

SQL Server allows users to configure many options of a session using SET command. The options configured through SET command affect the outcome of how queries are executed and the underlying behaviour of database engine.

At time we would like to know the current session SET options. But  there is no built statement/function which displays this information.

SQL Server only offers a metadata function @@OPTIONS which returns an integer that allows us to get the values that are set for the current session.

As the integer value returned by @@OPTIONS does not make much sense for most of the SQL Server users, we have written a simple bit wise operation SQL script which shows you all the SET options along with their status.

DECLARE @v_options INT

SELECT @v_options = @@OPTIONS

--DISABLE_DEF_CNST_CHK

IF ( (1 & @v_options) = 1 )

    PRINT 'DISABLE_DEF_CNST_CHK: ON'

ELSE

    PRINT 'DISABLE_DEF_CNST_CHK: OFF'

--IMPLICIT_TRANSACTIONS

IF ( (2 & @v_options) = 2 )

    PRINT 'IMPLICIT_TRANSACTIONS: ON'

ELSE

    PRINT 'IMPLICIT_TRANSACTIONS: OFF'

--CURSOR_CLOSE_ON_COMMIT

IF ( (4 & @v_options) = 4 )

    PRINT 'CURSOR_CLOSE_ON_COMMIT: ON'

ELSE

    PRINT 'CURSOR_CLOSE_ON_COMMIT: OFF'

--ANSI_WARNINGS

IF ( (8 & @v_options) = 8 )

    PRINT 'ANSI_WARNINGS: ON'

ELSE

    PRINT 'ANSI_WARNINGS: OFF'

--ANSI_PADDING

IF ( (16 & @v_options) = 16 )

    PRINT 'ANSI_PADDING:ON'

ELSE

    PRINT 'ANSI_PADDING: OFF'

--ANSI_NULLS

IF ( (32 & @v_options) = 32 )

    PRINT 'ANSI_NULLS: ON'

ELSE

    PRINT 'ANSI_NULLS: OFF'

--ARITHABORT

IF ( (64 & @v_options) = 64 )

    PRINT 'ARITHABORT: ON'

ELSE

    PRINT 'ARITHABORT: OFF'

--ARITHIGNORE

IF ( (128 & @v_options) = 128 )

    PRINT 'ARITHIGNORE: ON'

ELSE

    PRINT 'ARITHIGNORE: OFF'

--QUOTED_IDENTIFIER

IF ( (256 & @v_options) = 256 )

    PRINT 'QUOTED_IDENTIFIER: ON'

ELSE

    PRINT 'QUOTED_IDENTIFIER: OFF'

--NOCOUNT

IF ( (512 & @v_options) = 512 )

    PRINT 'NOCOUNT: ON'

ELSE

    PRINT 'NOCOUNT: OFF'

--ANSI_NULL_DFLT_ON

IF ( (1024 & @v_options) = 1024 )

    PRINT 'ANSI_NULL_DFLT_ON: ON'

ELSE

    PRINT 'ANSI_NULL_DFLT_ON: OFF'

--ANSI_NULL_DFLT_OFF

IF ( (2048 & @v_options) = 2048 )

    PRINT 'ANSI_NULL_DFLT_OFF: ON'

ELSE

    PRINT 'ANSI_NULL_DFLT_OFF: OFF'

--CONCAT_NULL_YIELDS_NULL

IF ( (4096 & @v_options) = 4096 )

    PRINT 'CONCAT_NULL_YIELDS_NULL: ON'

ELSE

    PRINT 'CONCAT_NULL_YIELDS_NULL: OFF'

--NUMERIC_ROUNDABORT

IF ( (8192 & @v_options) = 8192 )

    PRINT 'NUMERIC_ROUNDABORT: ON'

ELSE

    PRINT 'NUMERIC_ROUNDABORT: OFF'

--XACT_ABORT

IF ( (16384 & @v_options) = 16384 )

    PRINT 'XACT_ABORT: ON'

ELSE

    PRINT 'XACT_ABORT: OFF'

Here is the output when the above code is executed for my session

DISABLE_DEF_CNST_CHK: OFF
IMPLICIT_TRANSACTIONS: OFF
CURSOR_CLOSE_ON_COMMIT: OFF
ANSI_WARNINGS: ON
ANSI_PADDING:ON
ANSI_NULLS: ON
ARITHABORT: ON
ARITHIGNORE: OFF
QUOTED_IDENTIFIER: ON
NOCOUNT: OFF
ANSI_NULL_DFLT_ON: ON
ANSI_NULL_DFLT_OFF: OFF
CONCAT_NULL_YIELDS_NULL: ON
NUMERIC_ROUNDABORT: OFF
XACT_ABORT: OFF