更改数据库中所有存储过程的 ANSI_NULLS 设置 [英] Change the ANSI_NULLS setting for all Stored Procedures in the Database
问题描述
我们在 ANSI_NULLS 设置和计算列方面存在一些问题,我们有大量的存储过程
We have some problems with the ANSI_NULLS setting and computed columns and we have a ton of stored procedures that have
SET ANSI_NULLS OFF
我们想将它们全部更改为
We want to change them all to
SET ANSI_NULLS ON
是否有一种简单的方法可以做到这一点,或者我必须将所有 SP 提取到脚本中,对其进行更改并再次运行以删除并重新创建所有 SP
Is there an easy way to do that or must I extract all the SPs to a script, change it and run it again to drop and recreate all the SPa
推荐答案
您必须编写所有过程的脚本,并在启用 ANSI_NULLS 的情况下重新创建它们.
You must script all the procedures, and re-create them with ANSI_NULLS on.
如果我有很多要做,我可能会向我的客户端应用程序添加一个功能.
If i had a lot to do, i might add a function to my client app.
伪代码:
procedure FixAllStoredProcedureAnsiNullness(connection)
{
Strings spNames = GetStoredProcedureNames(connection);
foreach spName in spNames
{
String sql = GetStoredProcedureSQL(connection, spName);
//turn on option for remainder of connection
connection.ExecuteNoRecords("SET ANSI_NULLS ON");
BeginTransaction(connection);
try
connection.ExecuteNoRecords("DROP PROCEDURE "+spName);
connection.ExecuteNoRecords(sql);
CommitTranasction(connection);
except
RollbackTransaction(connection);
raise;
end;
}
}
我在 SQL Server:如何在没有 DMO/SMO 的情况下生成对象脚本?
但通常我只会使用企业管理器,从存储过程列表的顶部开始:
But normally i'll just use Enterprise Manager, starting at the top of the stored procedure list:
- 返回
- Ctrl+主页
- Ctrl+V
- 点击确定
- 下
- 转到 1
我的剪贴板包含的位置:
Where my clipboard contains:
SET ANSI_NULLS ON
GO
如果您不幸被 SSMS 卡住了,那么您就是该 POS 的 SOL,IIRC.TWSS.
If you're unfortunate enough to be stuck with SSMS, then you're SOL with that POS, IIRC. TWSS.
这篇关于更改数据库中所有存储过程的 ANSI_NULLS 设置的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!