0

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.

1
  • Will you be using T-SQL for the actual search?
    – Hannah Vernon
    Apr 26, 2014 at 0:44

2 Answers 2

2

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.

1
  • It needs to allow other whitespace, such as tabs, in between the "Insert" and the "Into" words: [\s\t]*INSERT[\s\t\r\n]*(INTO|)[\s\t\r\n]*[A-Za-z0-9_."\[\]]*[\s\t\r\n]*
    – ubergeek
    Jul 26, 2015 at 20:21
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.