设置选项....它们存储在哪里 [英] SET OPTIONS....where are they stored

问题描述

快速问题...

数据库中存储的 SP、Func、触发器等的 SET OPTIONS 值在哪里?如果它们与全局设置不同?

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?

开启 ARITHABORT
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
设置 ANSI_NULLS ON
设置 ANSI_PADDING ON
设置 ANSI_WARNINGS ON
设置 NUMERIC_ROUNDABORT 关闭

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

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

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

使用 [SomeDB]
去

USE [SomeDB]
GO

设置 ARITHABORT 关闭
设置 CONCAT_NULL_YIELDS_NULL 关闭
SET QUOTED_IDENTIFIER 关闭
去

SET ARITHABORT OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET QUOTED_IDENTIFIER OFF
GO

创建 usp_SampleProc
AS
开始
-- 执行一些操作
结束

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')

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

其余的……它们甚至为每个存储过程存储……吗?
谢谢，
_Ub

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

推荐答案

那些适用于过程的方案，如 ANSI_NULLS 和 QUOTED_IDENTIFIER 在 sys.sql_modules，在那里检索它们来自 OBJECTPROPERTY.

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

那些适用于数据库并为每个数据库设置的内容可在 sys.databases.

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

那些适用于会话的内容可在 sys.dm_exec_sessions.

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

最终实际应用的内容取决于设置，并且至少可以说覆盖和默认值的规则很复杂.一些客户端驱动程序自动设置选项开/关.不同的选项和不同的默认值取决于客户端(ODBC、OleDB、SNAC、SqlClient 等).通用规则是:

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. 数据库选项会覆盖实例选项.
2. SET 选项会覆盖数据库选项.
3. 提示会覆盖 SET 选项.

这篇关于设置选项....它们存储在哪里的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！