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

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.