ANSI_NULLS and QUOTED_IDENTIFIERS mismatch When I export the entire list of stored procedures/functions, I noticed there is a mismatch between ANSI_NULLS/QUOTED_IDENTIFIERS settings.  Some have them on, others off.  I'm not explicitly setting this for the stored procedures or functions, so how does SQL determine whether to define on or off for these settings by default?
so how does SQL determine whether to define on or off for the ansi nulls by default?


BOL quotes - For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its originally used value and takes effect. When invoked inside a stored procedure, the setting of SET ANSI_NULLS is not changed.

You can use the sys.sql_modules to check  the stored procedure or function uses uses_ansi_nulls and uses_quoted_identifier settings 1 (ON) or 0 (OFF)

SELECT OBJECT_NAME([object_id]) as ObjectName, 
       uses_ansi_nulls, 
       uses_quoted_identifier
FROM sys.sql_modules
--WHERE OBJECT_NAME([object_id]) = 'any_sp/function_name_your_Want_to_Check'
ORDER BY object_id


Both ANSI_NULLS and QUOTED_IDENTIFIERS should be ON so that you dont see any unexpected surprises. Also, in newer versions of SQL Server, this two settings are defaulted to ON.