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
Best Answer
Those that apply to procedures, like
ANSI_NULLS
andQUOTED_IDENTIFIER
are insys.sql_module
s, where they are retrieved from byOBJECTPROPERTY
.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: