This seems simple enough… but I had to try a few methods to get it right.

Note: the solution here is the one I settled on because it’s the first one that worked for me. There probably are better solutions available. Feel free to comment if you have any.

I settled on using Notepad++, my go-to text editor. It has a Find in Files option that supports regex. Finding the right regex was not too hard:

(.*(\R)){5}

This will find all characters on the line (.*), the line ending (\R – because Notepad++ has PCRE support) and then find that combination 5 times ({5}).

I placed that regex in the Find what box in Notepad++ Find in Files dialog. I left Replace with blank and set the Filters to *.sql.

Here is how I set up the Find in Files in Notepad++ (version 7.1):

Notepad++ Find in Files screen shot
How I set up Notepad++ to remove the first 5 lines from all .sql files in a given folder.

I had 697 files to process and Notepad++ did that in under 2 minutes.

Background

I had created SQL scripts from a database, but SSMS had helpfully added a descriptive line and then a few unwanted SET statements, like so:

/*** COMMENTS ***/
SET SOMETHING
GO
SET SOMETHING
GO

Then, I also opted to have SSMS save the files in Unicode because, well, why not? Turns out the Windows command interpreter’s FOR /F doesn’t support UTF-8.

I could have regenerated the scripts, but I no longer had access to the source database server, so I had to deal with with I had available.

I hope this tip can help you, but I look forward to your comments on improving the solution!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s