更改数据库中所有存储过程的ansi_nulls设置

您必须编写所有过程的脚本,并在打开ansi_nulls的情况下重新创建它们。

如果我有 很多 为此,我可能会向我的客户端应用程序添加一个函数。

PseudoCode:

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的代码 SQL Server: How to generate object scripts without DMO/SMO?

但通常我会用 企业经理 ,从存储过程列表的顶部开始:

1. 返回
2. Ctrl键 + 家
3. Ctrl键 + V
4. 单击确定
5. 向下
6. 转到1

我的剪贴板包含:

SET ANSI_NULLS ON
GO

如果你不幸被SSMS卡住了,那你就是那个位置的SOL,IIRC。TWSS。

我们使用的解决方案是 伊恩 现在我们有了一个自动化的程序来解决这个问题。

下面是我们用于从数据库重新创建所有SP的最终代码:

public static class AnsiNullsManager
{

    public static void ReCreateAllStoredProcedures(SqlConnection connection, bool ansiNullsOn)
    {
        var sql =
            @"select object_name(sys.all_sql_modules.object_id) as Name, definition as Code
                from sys.all_sql_modules inner join sys.objects ON 
                sys.all_sql_modules.object_id = sys.objects.object_id
                where objectproperty(sys.all_sql_modules.object_id, 'IsProcedure') = 1 AND is_ms_shipped = 0 and uses_ansi_nulls = " +
            (ansiNullsOn ? "0" : "1") +
            "ORDER BY Name ";

        if (connection.State == ConnectionState.Closed)
            connection.Open();

        var sps = new List<SpObject>();

        var cmd = connection.CreateCommand();
        cmd.CommandText = sql;

        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                sps.Add(new SpObject(reader.GetString(0), reader.GetString(1)));
            }
        }

        var cmdSetAnsiNulls = connection.CreateCommand();
        cmdSetAnsiNulls.CommandText = "SET ANSI_NULLS " + (ansiNullsOn ? "ON" : "OFF") + ";";
        cmdSetAnsiNulls.ExecuteNonQuery();

        foreach (var sp in sps)
        {
            var trans = connection.BeginTransaction();

            try
            {

                var cmdDrop = connection.CreateCommand();
                cmdDrop.CommandText = "DROP PROCEDURE " + sp.Name;
                cmdDrop.Transaction = trans;
                cmdDrop.ExecuteNonQuery();



                var cmdReCreate = connection.CreateCommand();
                cmdReCreate.CommandText = sp.Code;
                cmdReCreate.Transaction = trans;
                cmdReCreate.ExecuteNonQuery();
                trans.Commit();

            }
            catch (Exception)
            {
                trans.Rollback();
                throw;
            }
        }

    }

    private class SpObject
    {
        public SpObject(string name, string code)
        {
            Name = name;
            Code = code;
        }

        public string Name { get; private set; }
        public string Code { get; private set; }
    }

}

只是想在那里发出警告。我无法想象为什么要为所有的SP设置ansi_nulls,但是如果它们中的任何一个以任何方式依赖于与空值的比较(可能会有很多不同的方式发生),那么当您更改该设置时,您的结果会有所不同。我建议在安全的环境中进行一些严格的回归测试。

到目前为止,最简单的方法是编写s的proc脚本,运行find和replace命令,然后再次运行proc定义。