SET OPTIONS....where are they stored

Question

Quick Questions...

Where are the values for SET OPTIONS stored in the database for a SP, Func, Trigger, etc? If they are different from the global settings?

SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF

I know the global settings for a database are stored in sys.databases view. But what about for each Stored Proc or other objects.

USE [SomeDB]
GO

SET ARITHABORT OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET QUOTED_IDENTIFIER OFF
GO

CREATE usp_SampleProc
AS
BEGIN
-- perform some action
END

I see that a couple could be retrived using:

SELECT OBJECTPROPERTY(OBJECT_ID('Procedure_Name'), 'ExecIsQuotedIdentOn')
SELECT OBJECTPROPERTY(OBJECT_ID('Procedure_Name'), 'ExecIsAnsiNullsOn')

where are the rest... are they even stored for each Stored Proc.....at all?
thanks,
_Ub

Answer 1

Those that apply to procedures, like ANSI_NULLS and QUOTED_IDENTIFIER are in sys.sql_modules, where they are retrieved from by OBJECTPROPERTY.

Those that apply to databases and are set per database are available in sys.databases.

Those that apply to sessions are available in sys.dm_exec_sessions.

In the end what actually gets applied depends from setting to setting, and the rules of overwrites and defaults are complex to say the least. Some client drivers set options on/off automatically. Not the same options and not the same defaults, depends from client to client (ODBC, OleDB, SNAC, SqlClient etc). The generic rule is:

1. A database option overrides an instance option.
2. A SET option overrides a database option.
3. A hint overrides a SET option.