Need help phrasing regular expression to find particular T-SQL pattern

Question

I know this is a Database Forum, but since the text I am trying to find is SQL-specific, I am hoping that somebody can point me in the right direction. I need to search code to find places where the INSERT queries are written without explicitly specifying table column names. For example, I need to find things like this:

INSERT tablename SELECT col1, col2 FROM anothertable

INSERT INTO tablename VALUES (1), (2)

In case you wonder, when the column names are not explicitly specified after the table name, the SQL Server will implicitely assume that all of the columns are listed. As a result, if you add a new column to the table, your code will break.

The regex pattern I envision would look for the following:

INSERT followed by any characters, followed by either SELECT or VALUES where there is no occurrence of closing parenthesis ")" before the SELECT/VALUES.

I was thinking about alternative way to phrase it, but I am not sure it's easier:

INSERT or INSERT INT followed by space/tab/cr/lf, followed by any number of characters, followed by space/tab/cr/lf, NOT followed by open parenthesis "("

I appreciate any pointers.

Answer 1

The "magic" regexp to do what you want is:

^[\s\t]*INSERT( INTO|)[\s\t\r\n]*[A-Za-z0-9_."\[\]]*[\s\t\r\n]*(SELECT|VALUES)\s

I tested some scenarios in "The Regex Coach" (a free software to test regexp). I made sure it also works for tables like [dbo].[tablename] or "tablename" etc.

Answer 2

If you not want to use LIKE statment to match INSERTS, you can enable regular expression using CLR - please see below:

http://www.codeproject.com/Articles/42764/Regular-Expressions-in-MS-SQL-Server