Using automation for gathering release changes

If you need to gather release changes when creating installers, the script below can help you. Script simply gets all or current database release changes stored in file. I use it to automatically gather the latest changes after TeamCity builds release installer. The changes could be sent to PR for documentation. You can of course use more complicated patterns when extracting script text when required. To execute it, just pass in update script location, version number and email addresses.

 param (
 [string]$ScriptFolder = "C:\Database\Updates",
 [string]$Version = "2.11.0.0",
 [string]$SendEmailsTo = "test@test.com",
 [boolean]$GetAllChanges = 0
)

#sends notification emails
 Function SendEmail($SendEmailsTo_, $EmailSubject_, $changes_)
 {
   $emailFrom = "teamcity@test.com" 
   $smtpserver="smtp.test.com" 
   $smtp=new-object Net.Mail.SmtpClient($smtpServer) 

   foreach ($email in $SendEmailsTo_.split(';'))
   {   
      $smtp.Send($emailFrom, $email, $EmailSubject_, $changes_)
   }
 }

 #get file by version number
 Function GetReleaseFileName($Version_)
 {
    $VersionFilename_ ="v";
     $Version_.split('.') | ForEach-Object{
        $VersionFilename_ +=  [System.Convert]::ToDouble($_).ToString("00"); 
    }
    $VersionFilename_ += ".sql";

     #format e.g. v12.12.00.10.sql
    return $VersionFilename_;
 }

#return if no emails added
if($SendEmailsTo.trim() -eq "") { "no emails defined!"; return;}

$VersionFilename =  GetReleaseFileName $Version;

$EmailSubject = "Release $Version database changes";
$changes = "Changes in release file $VersionFilename `r`n `r`n";
$hasChanges = 0;

if(!$GetAllChanges) {  
    (dir $ScriptFolder) | sort CreationTime -Descending |
     ForEach-Object {  
        $fileContent = get-content ($_.Directory.FullName + "\" + $VersionFilename) -Raw;

        $patterns = 'alter','drop','insert','delete','update','create'; #paterns/strings to find
        $hasChanges = 0;
        foreach ($pattern in $patterns)
        {
           $fileContent.split("`r`n") | ?  { $_ -match $pattern } | % { $changes += $_.trim() + "`r`n"; $hasChanges = 1; } 
        } 

        if($hasChanges -eq 0) { $changes += "`r`nNo database changes for release $Version !"}

        write-output $changes

        SendEmail $SendEmailsTo $EmailSubject $changes;

        break;        
    }
}
else
{
  #parse all files for all previous releases
  $EmailSubject = "All database changes";
  $changes = "All database changes: `r`n `r`n";

  (dir $ScriptFolder) | sort CreationTime -Descending |
    ForEach-Object {  
        $fileContent = get-content ($_.Directory.FullName + "\" + $_.name) -Raw;

        $patterns = 'alter','drop','insert','delete','update','create';
        $changes += "`r`nChanges in release file $_`r`n";
        $hasChanges = 0;

        foreach ($pattern in $patterns)
        {          
           if($fileContent)
           {
              $fileContent.split("`r`n") | ?  { $_ -match $pattern } | % { $changes += $_.trim() + "`r`n"; $hasChanges = 1; }  
           } 
        } 

        if($hasChanges -eq 0) { $changes += "`r`nNo database changes for release " + $_.name.Replace(".sql","").Replace("v0","");}

        $changes += "`r`n-------------------------------`r`n";      
    }

    write-output $changes 

    SendEmail $SendEmailsTo $EmailSubject $changes;
}

I have included full script sample below
release_changes_notifier