Powershell Scripts to Replace Key Value Pair in SQL Script File Before Running the SQL Scripts

Consider having the 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 the 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 the 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