Change the ANSI_NULLS parameter for all stored procedures in the database

Question

We have some problems setting ANSI_NULLS 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 this or should I extract all the SPs in the script, change it and run it again to delete and recreate all the SPa

Answer 1

You must script execute all the procedures and recreate them using ANSI_NULLS.

If I had a lot to do, I could add a function to my client application.

pseudo code:

 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 program SQL stored procedures on SQL Server: how to generate object scripts without DMO / SMO?

But usually I just use Enterprise Manager , starting at the top of the list of stored procedures:

• Return
• Ctrl + Home
• Ctrl + V
• Click OK
• Down
• Go to 1

Where my clipboard contains:

 SET ANSI_NULLS ON GO 

If you are so sorry that you are stuck in SSMS, then you are SOL with this POS, IIRC. TWSS.

Answer 2

The solution that we use was published by Ian , and now we have an automatic 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 3

Just wanted to throw a warning. I can’t imagine why you had ansi_nulls for ALL of your SPs, but if any of them expected to compare to NULL in any way (and there could be many different ways that could happen), your results will differ when change this setting. I recommend rigorous regression testing in a safe environment.

Answer 4

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