Ansi Nulls和引用确定 [英] Ansi Nulls and quoted identified
问题描述
大家好，
SQL Server 2000.我有一个数据库，其中有几个对象
，其中包含ansi nuls并且引用标识符已打开。有没有一种方法我可以生成一个脚本：
（1）可以识别数据库中具有这两个
属性的所有对象打开并
（2）更改这些对象的属性并为这些对象打开ansi nulls和
引用标识符。
我试图避免通过gazillions对象和手动
这样做。
感谢您的帮助。
Raziq。
***通过Developersdex发送 http://www.developersdex.com ***
Hello everyone,
SQL Server 2000. I have a database in which there are several objects
which have ansi nuls and quoted identifier turned ON. Is there a way I
can generate a script which:
(1) Can identify all objects within the database that have those two
properties turned ON and
(2) Change the properties for these objects and turn the ansi nulls and
quoted identifier OFF for those objects.
I am trying to avoid going throuh gazillions of objects and manually
doing this.
Thanks for any help.
Raziq.
*** Sent via Developersdex http://www.developersdex.com ***
推荐答案
您确定要这样做吗？强烈建议将这两个选项始终设置为ON。更改这些选项将破坏
索引视图和使用它们的其他代码。只有你没有其他选项的遗留代码才能使用OFF设置
。
当你使用视图，过程等时，这些设置会保留创造
他们。我认为最简单的改变方法是使用Enterprise
Manager编写数据库脚本，然后在
结果脚本中搜索和替换。
-
David Portas
SQL Server MVP
-
Are you sure you want to do this? It is STRONGLY recommended that both
these options always be set ON. Changing these options will break
indexed views and other code that uses them. Only use the OFF setting
for legacy code where you don''t have another option.
These settings are persisted with views, procs, etc when you create
them. I think the easiest way to change them is to use Enterprise
Manager to script the database, then search and replace in the
resulting script.
--
David Portas
SQL Server MVP
--
要识别对象，请使用以下内容：
SELECT name，xtype，
OBJECTPROPERTY（id，''ExecIsAnsiNullsOn' '），
OBJECTPROPERTY（id，''ExecIsQuotedIdentOn''）
来自sysobjects WHERE xtype IN（''P''，''TR''，''V ''，''如果''，''FN''，''TF''）
要更改这些属性，您应该使用
重新创建这些对象
更改程序/更改查看/更改功能。
如果你不想一个接一个地做这个（如果你特别感觉的话）
幸运的是，至于更改系统表中未记录的列），我将给你一个提示：第30和29位（bi在sysobjects表中
状态列的t掩码：0x60000000。
当然，更改系统表是一个坏主意（它可能会导致数据）
丢失或可能阻止SQL Server实例运行）。更改
系统表的未记录列是一个更糟糕的想法！
Razvan
To identify the objects, use something like this:
SELECT name, xtype,
OBJECTPROPERTY(id,''ExecIsAnsiNullsOn''),
OBJECTPROPERTY(id,''ExecIsQuotedIdentOn'')
FROM sysobjects WHERE xtype IN (''P'',''TR'',''V'',''IF'',''FN'',''TF'')
To change these properties, you should re-create those objects using
ALTER PROCEDURE / ALTER VIEW / ALTER FUNCTION.
If you do not want to do this one by one (and if you feel particularly
lucky today, as to change an undocumented column in a system table), I
will give you a hint: bits 30 and 29 (bit mask: 0x60000000) of the
status column in the sysobjects table.
Of course, changing a system table is a bad idea (it may cause data
loss or may prevent an instance of SQL Server from running). Changing
an undocumented column of a system table is an even worse idea !
Razvan
如果你不想一个接一个地做这个，如果你觉得特别好
今天自杀...
Razvan，你真的有吗？测试了你暗示的建议？我会
绝不建议任何人尝试直接修改系统表。在
这种情况下完全没必要。
-
David Portas
SQL Server MVP
-
If you do not want to do this one by one and if you feel particularly
SUICIDAL today...
Razvan, have you actually tested out your implied suggestion? I would
NEVER recommend anyone to attempt to modify system tables directly. In
this case it is completely unnecessary.
--
David Portas
SQL Server MVP
--
这篇关于Ansi Nulls和引用确定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
