Change the ANSI_NULLS setting for all Stored Procedures in the Database

sql-server, stored-procedures

Solution

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:

- Return

- Ctrl+Home

- Ctrl+V

- Click OK

- Down

- 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.

Problem

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

Original source