设置选项......它们存储在哪里

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?

对于SP，Func，Trigger等，数据库中存储的SET OPTIONS的值在哪里？如果它们与全局设置不同？

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

设置CONCAT_NULL_YIELDS_NULL上的SET ARITHABORT 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.

我知道数据库的全局设置存储在sys.databases视图中。但是对于每个Stored Proc或其他对象呢。

USE [SomeDB]
GO

使用[SomeDB] GO

SET ARITHABORT OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET QUOTED_IDENTIFIER OFF
GO

SET ARITHABORT OFF SET CONCAT_NULL_YIELDS_NULL OFF SET QUOTED_IDENTIFIER OFF GO

CREATE usp_SampleProc
AS
BEGIN
-- perform some action
END

创建usp_SampleProc AS BEGIN - 执行一些操作END

I see that a couple could be retrived using:

我看到一对夫妇可以使用以下方式重审：

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

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

其余的......他们甚至存储了每个存储过程.....根本没有？谢谢，_Ub

1 个解决方案

#1

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

适用于过程的那些（如ANSI_NULLS和QUOTED_IDENTIFIER）位于sys.sql_modules中，它们由OBJECTPROPERTY检索。

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

sys.databases中提供了适用于数据库并按数据库设置的数据库。

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

适用于会话的那些在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:

最后实际应用的内容取决于从设置到设置，并且覆盖和默认的规则至少是复杂的。某些客户端驱动程序自动设置选项的开/关。不同的选项和不同的默认值，取决于客户端到客户端（ODBC，OleDB，SNAC，SqlClient等）。通用规则是：

1. A database option overrides an instance option.
2. 数据库选项会覆盖实例选项。
3. A SET option overrides a database option.
4. SET选项会覆盖数据库选项。
5. A hint overrides a SET option.
6. 提示会覆盖SET选项。