Mixed ANSI Null setting driving me Nuts!

I inherited a SQL database that had a nasty habit started before someone could make all developers follow a few simple rules. Rules, such as all tables must have a primary key and also most should have a clustered index. Another issue I ran into was when I tried to created an indexed view. It was great until I tried to save a record using a save SP that had a different ANSI setting. 

The issue was I have some SPs and functions using ANSI null off explicitly and some using ANSI null on. I have been working around this issue for the last 7 months, waiting for the go ahead to take all the SPs and functions and have them all have ANSI null on. The game grew old this week when for the 'unteenth' time I get a text message and e-mail with the error message could not save due to ANSI null setting.

SET ANSI_NULLS ON

My solution while not the best was effective.

1. Find all objects with ANSI_NULLS OFF
2. Script them to a file
3. Make a backup copy of the file
4. Search and replace ANSI_NULLS OFF to ANSI_NULLS ON
5. save the file
6. Make a backup
7. Execute the file
8. Verify all objects have the same setting
9. Repeat on Dev, Test, and Production
10. If there are any issues, restore the backup.

I used the query

select object_name(object_id) from sys.sql_modules where uses_ansi_nulls=0
order by  object_name(object_id)

to identify all the objects with ANSI NULL OFF

For Stored procedures I was able to use SQL Server Management Studio Object Explorer Details. I selected all the user defined SPs and selected drop and create script results to a file. Make sure you have script permissions set to yes. When dropping and recreating the permissions are lost.

Then, you wait, go get some coffee or something because scripting 500+ Sps takes some time. I made sure to have the permissions included also. If you have less objects then it maybe faster but be patient. After this completes do the same for the functions. Once completed, open the files with a good editor such as notepad++, or whatever you favorite search and replace editor is. Once this is done we can combine the files as they will have 'GO' statements in them separating each object into a batch. If something fails we have a point of reference as we can click the error and SSMS will take use to the offending object. This could happen if a table or view has changed and the function or SP was not recompiled.


Make sure to save these changes in source control too.