Regular expression

whiteboard

Looking for a regex that would search sql scripts for

Any instance of the words create alter or drop
But exclude comments /** or --

Some success with (Alter)*(create)
Can't find an example (in a hurry to produce a list of objects promoted to test) that could do this.

Any help or expressions appreciated.

Whitey

bpmurray

You want a regex to include in a program, or do you want a script to generate something? Do you want to change these verbs or do you want to simply highlight them? For example using grep you could do this:

grep -i "create\|alter\|drop" filename.sql

Of course, the choice of tools is limited to whatever is available on your chosen platform, so Windows is very poor, while Mac and Linux/UNIX are fine.

5rtytry56

OP, what flavour of SQL are you using?
MySQL, SQL Server

whiteboard

the files to be searched are in a folder structure
was looking to us the 'find in file' feature in VS which I find far more reliable than windows search.

requirements
search all files in a folder/sub-folder structure
for all files containing instances create alter or drop

of course - open to other methods of doing this

bpmurray

Try the following, using either Linux, or cygwin or similar on Windows

grep -ril "create\|alter\|drop" *

It will return a list of files containing one of the strings, case insensitive, recursing into subdirectories

Skrynesaver

In Linux/Cygwin, with checks for comments

for $file in $(find /folder/subfolder -type f)
do
  perl -ne '$in_comment++ if/\/\*/;$incomment-- if /\*\//;if (! $incomment){ if (print /(create|alter|drop)/ && !/--.+(create|alter|drop));}' 
done