Powershell Scripts to replace Key value pair in SQL script file before running the SQL scripts

Consider having a following SQL file:

C:\PowershellTest\UpdateImagesLocation.sql

DECLARE @ImagesLocation NVARCHAR(max)
SET @ImagesLocation = 'C:\ImagesStore\'

.....

Now, when you run this sql scripts file during the deployments, you would also want to change the value of @ImagesLocation, as the location may vary for different environment.

This can be achieved through using regular expressions in Powershell scripts.
In order to do that, you can create a following function in your Powershell deployment or pre-deployment scripts:

#if an SQL file contains 'SET @variable_name=value', then this function can be called to replace value by actual value.

function replacePatternMatchingValueInFile( $file, $key, $value ) {

    $content = Get-Content $file

    if ( $content -match "^$key\s*=" ) {

        $content -replace "^$key\s*=.*", "$key = $value" |

        Set-Content $file     

    } else {

        Add-Content $file "$key = $value"

    }

}

Call this function in a following manner:

$scriptfile = "C:\PowershellTest\UpdateImagesLocation.sql"

replacePatternMatchingValueInFile $scriptfile"SET @ImagesLocation" "'\\datashare\appImages'"

As a result, the variable assignment for @ImagesLocation would be changed to a different value in the sql file.

CodeProject