finding instance of text in sql files after WHERE clause

Question

Hi guys,

I have a VS2008 solution with a database project, in it is a folder that holds a whole bunch of sql stored procedures (we are talking 100's).

Due to a change that has been made to the ANSI_NULLS setting for all the sprocs I need to update all instances of '= null' to 'is null'.

I cannnot do a "find and replace all" on .sql files as most instances of this string are located in the sproc declarations, i.e., "@dtDate DateTime = null" (which i dont't want to change). However all other instances such as "if (@dtDate = null)" I do want to change as it is no longer valid.

What is a good way to replace all instances of the text "= null" to "is null" that occur after a particular word (e.g., "WHERE") in all .sql files in a database project folder?

Thanks very much

Answer 1

I ended up using a little bit of regex in the VS2008 find dialog.

create(.|\n)*select(.|\n)*= null

This found instances of "= null" after the select statement (missing instances in the declarations). I then just went through each instance manually - worked OK.