In SQL Server, you can use the SESSIONPROPERTY() function to return the SET options settings of a session.

These are things such as your ANSI_NULLS setting, ANSI_PADDING, QUOTED_IDENTIFIER, etc.

To do this, you simply pass the desired setting name to the function as a string, while selecting the function.

Example

Here’s an example to demonstrate. In this example, I check my ANSI_NULLS setting.

SELECT SESSIONPROPERTY ('ANSI_NULLS');

Result:

1

My session had ANSI_NULLS set to ON when I ran that, and so the result was 1.

If I change my ANSI_NULLS setting to OFF, I get a different result.

SET ANSI_NULLS OFF;
SELECT SESSIONPROPERTY ('ANSI_NULLS');

Result:

0

The zero means OFF.

Arguments

The SESSIONPROPERTY() function accepts the following arguments.

Option	Description
ANSI_NULLS	Specifies whether the ISO behaviour of equals (=) and not equal to (<>) against null values is applied.
ANSI_PADDING	Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in character and binary data.
ANSI_WARNINGS	Specifies whether the ISO standard behaviour of raising error messages or warnings for certain conditions, including divide-by-zero and arithmetic overflow, is applied.
ARITHABORT	Determines whether a query is ended when an overflow or a divide-by-zero error occurs during query execution.
CONCAT_NULL_YIELDS_ NULL	Controls whether concatenation results are treated as null or empty string values.
NUMERIC_ROUNDABORT	Specifies whether error messages and warnings are generated when rounding in an expression causes a loss of precision.
QUOTED_IDENTIFIER	Specifies whether ISO rules about how to use quotation marks to delimit identifiers and literal strings are to be followed.

These all return either 1 or 0 (with 1 meaning ON, and 0 meaning OFF).

Any other value results in NULL being returned.