PowerShell – Execute SQL scripts against SQL Server

2015/03/18

Because I’m lazy and/or I don’t have time to format the code, above is a screenshot for clarity, below is the code for whoever needs it.

function Invoke-CustomSqlQuery($connectionString, $query) {
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString
    Register-ObjectEvent -inputobject $connection -eventname InfoMessage -action {
        write-host $event.SourceEventArgs
    } | Out-Null

    Write-Host “Connecting to database …”
    try {
        $connection.Open()
    
        Write-Host “Executing SQL script…”

        $queries = [System.Text.RegularExpressions.Regex]::Split($query, “\s*go\s*|\s*GO\s*|\s*Go\s*”, [System.Text.RegularExpressions.RegexOptions]::IgnoreCase)
    
        $queries | ForEach-Object {
            $q = $_
            if ((-not [String]::IsNullOrWhiteSpace($q)) -and ($q.Trim().ToLowerInvariant() -ne “go”)) {
                    $command = $connection.CreateCommand()
                    $command.CommandText = $q
                    $command.ExecuteNonQuery() | Out-Null
            }
        }
    }
    finally {
        Write-Host “Closing database connection”
        $connection.Dispose()
    }
}

Sample for $connectionString variable

Server=Server_name;uid=usrname;pwd=passw;Initial Catalog=The_Database;Integrated Security=False;

Note 0: The initial regular expression for splitting after GO keyword (as displayed in the image) is wrong, the correct version is updated in the code block. I had to use “\s*go\s*|\s*GO\s*|\s*Go\s*” because from some reason, PowerShell didn’t want to interpret correctly what I’ve initially found by google-ing (the expression containing start and end delimiters)

Note 1: Credits go to StackOverflow, IT blogs and alike, sites that helped me with the code when searching for solutions.
Note 2: Be aware that when copy-pasting, the quotes and possibly other characters get messed up by WordPress, you’ll have to replace them.


%d bloggers like this: