Change the ANSI_NULLS setting for all Stored Procedures in the Database

Question

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

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

Answer 1

By far the easiest way is to script the s'procs, run find and replace command, then run the proc definitions again.

Answer 2

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.

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;
   }
}

i had code on how to get the SQL of a stored procedure programatically on SQL Server: How to generate object scripts without DMO/SMO?

But normally i'll just use Enterprise Manager, starting at the top of the stored procedure list:

1. Return
2. Ctrl+Home
3. Ctrl+V
4. Click OK
5. Down
6. Goto 1

Where my clipboard contains:

SET ANSI_NULLS ON
GO

If you're unfortunate enough to be stuck with SSMS, then you're SOL with that POS, IIRC. TWSS.

Answer 3

The solution that we use was the posted by Ian and now we have an automated procedure to solve the problem.

Here is the final code that we use to recreate all the SPs from the database:

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; }
    }

}

Answer 4

Just wanted to throw a warning in there. I can't imagine why you had ansi_nulls set off for ALL your SPs but if any of them were counting on comparisons to NULL in any way (and there can be a lot of different ways that could happen) your results will different when you change that setting. I recommend some rigorous regression testing in a safe environment.