Replacing SQL Text within a script file using regular expressions and Powershell

I recently needed to update a load of scripts in our Source Control to remove the filegroup declaration from ON [PRIMARY] to just not be declared, so that new objects would be deployed to the default filegroup on some new databases we had created. Personally, I like to keep user tables away from the system tables to assist with being able to do piecemeal restores, and allow easier management of database files.

From looking at the scripts, written by lots of different devs, there were various different standards and styles that had been applied, from not declaring a filegroup (yey no work for me!), to differing numbers of spaces between the ON and [PRIMARY] keywords:-

  • ON   [PRIMARY]

All in all, there were a few hundred scripts that I needed to make this change to and I had absolutely no intention of doing that manually. So: to Powershell!

The basics of this script were going to be easy:- loop through a given directory, check the contents of a relatively small text file, look for ON [PRIMARY] clause and replace it with nothing. The loop will be really easy, we can just use the get-childitem cmdlet to get the list of files, and then use a ForEach loop to cycle through the files:-

$FilesToCheck = get-childitem $Directory | where-object {$_.PSIsContainer -eq $false}
   #check each file
    ForEach ($File in $FilesToCheck)

Note the where clause to remove any directories from the items found. We could also add filters and so on here, however, at this point I want to check every file in the directory so I haven’t coded for that.

Next, I need to open the file and check the content. My first thought was to use the get-content cmdlet, however, I had heard some “bad” things about this in terms of performance, so after a little bit of reading, I decided to use the .NET System.IO.File class and the ReadAllText method. The code snippet for this is below:

$FileContent = [System.IO.File]::ReadAllText($File.FullName)

Note that in powershell, once you’ve got as far as typing [System.IO.File]::, you can then use the TAB key to cycle through all the available methods you can call on that object; or, if like me, you tend to code in the ISE, then intellisense should display all the available methods for you.

So, we’ve now got an object with all the files we want to check, and a way of opening the content of the file and storing that into another object so that we can do something with it. Next up: the hard bit, replacing the string, but with an arbitrary number of spaces between the ON and the [PRIMARY] keywords.

For this, I decided to use a regular expression. I’l be honest, I don’t do enough coding using these to know the syntax by heart, so I used RegExLib’s CheatSheet coupled with RegExHero’s Regex Tester to build up the regex statement I needed:-


This seems to work ok; key points to note are that square brackets are control characters in a regular expression, and so need to be escaped by a slash and that adding the + after the space indicates match one or more of that match together, essentially my multiple spaces between my keywords. I then create a new object of they type System.Text.RegularExpression.RegEx with the string that I’ve built up above.

Using this object, I can now perform my replace statement. I find the replace with a Regular Expression slightly counterintuitive as you call it on the Regular Experssion rather than on the object you want to replace. However, the syntax for this is really straight forwards:-

$NewContent = $RegexSearch.Replace($FileContent, $ReplacementText)

This leaves me with a new object where any instance of my regular expression will be replaced with the replacement text I specify.

Lastly, I need to overwrite the file. This again uses the System.IO.File class, this time with the WriteAllText method. I’m also going to wrap this with an if statement based on a test variable; if we are in test mode we’ll just print our statement rather than actually updating the file:-

if($Test -eq $false)
     #update the file with the new content

The whole function is below; this took less than a second to run against my directory with 100+ files in; tweaked 2017/02/10 to add in exclusions and option to recurse through folders.

function Update-FileContentReplaceRegex
            [string]$RegexString = "ON[ ]+\[PRIMARY\]",
            [string]$ReplacementText = "",
            [string]$Directory = 'C:\DarwinM_TFS\TM-IT-Products\Banking\Main\Release Scripts\TMGBanking\',
            [bool]$Test = $true,
            [bool]$Recurse = $false,
            [string[]]$Exclude = ''

    #verbose what we're checking
    write-verbose "Checking $Directory for the regex: $RegexString"

    #create the regex string we want to search for
    $RegexSearch = new-object System.Text.RegularExpressions.regex($RegexString)

    #get the files we want to check
    $FilesToCheck = get-childitem $Directory -recurse:$recurse -exclude $Exclude | where-object {$_.PSIsContainer -eq $false}

    #check each file
    ForEach ($File in $FilesToCheck)
        #open the file content
        $FileContent = [System.IO.File]::ReadAllText($File.FullName)
        #if the regex string is found
        if ($RegexSearch.IsMatch($FileContent))
            #If in verbose mode print the file we're checking   
            write-verbose "Checking file $($File.FullName)"

            #Do the replace into an object
            $NewContent = $RegexSearch.Replace($FileContent, $ReplacementText)
            #write an output message
            write-verbose "Updating file $($File.FullName) from:"
            write-verbose $FileContent
            Write-verbose "To:"
            write-verbose $NewContent
            if($Test -eq $false)
                #update the file with the new content
            #file not found
            write-verbose "Regex not found in file $($File.Fullname)"

export-modulemember -Function Update-FileContentReplaceRegex

2 thoughts on “Replacing SQL Text within a script file using regular expressions and Powershell

Leave a Reply

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

You are commenting using your 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