Read current SET option statuses 1

[eksortso]

How do I retrieve the settings for the various SET options?

Please forgive me if this is too simple a question, but I cannot find a solution anywhere.

 

[Ankor]

Did you try to search Books Online for "set options"?

 

[jms8222]

Try "EXEC sp_configure" and see if that is what you are looking for.

 

[eksortso]

Books Online explains setting the connection-level SET options at length. But it doesn't say how to view them. sp_configure shows lower-level options, but not the ones that I'm looking for.

I'm looking for these connection-level SET options (e.g. ARITHABORT, QUOTED_IDENTIFIER, etc.), including the ones which can be changed temporarily inside stored procedures. Is it possible to look at the settings of these options?

 

[jms8222]

If you are looking for the connection-level options, then you would want to use

EXEC sp_dboption @dbname='YourDB'

This lists all the options currently on. If you want to look at a specific option, add the parameter @optname='arithabort'.

 

[eksortso]

Thanks, jms8222! Have a star!

I've since discovered that the SESSIONPROPERTY function can be used to check some specific options. But your method shows the active options all at once.