SET OPTIONS....where are they stored
sql-server, sql-server-2005
Solution
Those that apply to procedures, like `ANSI_NULLS` and `QUOTED_IDENTIFIER` are in `sys.sql_module`s, 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:
- A database option overrides an instance option.
- A SET option overrides a database option.
- A hint overrides a SET option.
Problem
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