Import-Module dbatools
Set-StrictMode -Version 2

function Get-InsecureCredential {
  param ($Username, $Password)
  Write-Debug "Get-InsecureCredential."
  $SecurePassword = $Password | ConvertTo-SecureString -AsPlainText -Force
  $Credential = New-Object System.Management.Automation.PSCredential ($Username, $SecurePassword)
  return $Credential
}
function Get-SqlServerCollations {
  param ($Filter)
  $Collations = @(
    "SQL_Latin1_General_Cp437_CS_AS_KI_WI",
    "SQL_Latin1_General_Cp437_CI_AS_KI_WI",
    "SQL_Latin1_General_Pref_Cp437_CI_AS_KI_WI",
    "SQL_Latin1_General_Cp437_CI_AI_KI_WI",
    "SQL_Latin1_General_Cp437_BIN",
    "SQL_Latin1_General_Cp850_BIN",
    "SQL_Latin1_General_Cp850_CS_AS_KI_WI",
    "SQL_Latin1_General_Cp850_CI_AS_KI_WI",
    "SQL_Latin1_General_Cp850_CI_AI_KI_WI",
    "SQL_Latin1_General_Pref_Cp850_CI_AS_KI_WI",
    "SQL_1xCompat_Cp850_CI_AS_KI_WI",
    "SQL_Latin1_General_Cp1_CS_AS_KI_WI",
    "SQL_Latin1_General_Cp1_CI_AS_KI_WI",
    "SQL_Latin1_General_Pref_Cp1_CI_AS_KI_WI",
    "SQL_Latin1_General_Cp1_CI_AI_KI_WI",
    "SQL_AltDiction_Cp850_CS_AS_KI_WI",
    "SQL_AltDiction_Pref_Cp850_CI_AS_KI_WI",
    "SQL_AltDiction_Cp850_CI_AI_KI_WI",
    "SQL_Scandainavian_Pref_Cp850_CI_AS_KI_WI",
    "SQL_Scandainavian_Cp850_CS_AS_KI_WI",
    "SQL_Scandainavian_Cp850_CI_AS_KI_WI",
    "SQL_AltDiction_Cp850_CI_AS_KI_WI",
    "SQL_Latin1_General_1250_BIN",
    "SQL_Latin1_General_Cp1250_CS_AS_KI_WI",
    "SQL_Latin1_General_Cp1250_CI_AS_KI_WI",
    "SQL_Czech_Cp1250_CS_AS_KI_WI",
    "SQL_Czech_Cp1250_CI_AS_KI_WI",
    "SQL_Hungarian_Cp1250_CS_AS_KI_WI",
    "SQL_Hungarian_Cp1250_CI_AS_KI_WI",
    "SQL_Polish_Cp1250_CS_AS_KI_WI",
    "SQL_Polish_Cp1250_CI_AS_KI_WI",
    "SQL_Romanian_Cp1250_CS_AS_KI_WI",
    "SQL_Romanian_Cp1250_CI_AS_KI_WI",
    "SQL_Croatian_Cp1250_CS_AS_KI_WI",
    "SQL_Croatian_Cp1250_CI_AS_KI_WI",
    "SQL_Slovak_Cp1250_CS_AS_KI_WI",
    "SQL_Slovak_Cp1250_CI_AS_KI_WI",
    "SQL_Slovenian_Cp1250_CS_AS_KI_WI",
    "SQL_Slovenian_Cp1250_CI_AS_KI_WI",
    "SQL_Latin1_General_1251_BIN",
    "SQL_Latin1_General_Cp1251_CS_AS_KI_WI",
    "SQL_Latin1_General_Cp1251_CI_AS_KI_WI",
    "SQL_Ukrainian_Cp1251_CS_AS_KI_WI",
    "SQL_Ukrainian_Cp1251_CI_AS_KI_WI",
    "SQL_Latin1_General_1253_BIN",
    "SQL_Latin1_General_Cp1253_CS_AS_KI_WI",
    "SQL_Latin1_General_Cp1253_CI_AS_KI_WI",
    "SQL_Latin1_General_Cp1253_CI_AS_KI_WI",
    "SQL_Latin1_General_Cp1253_CI_AI_KI_WI",
    "SQL_Latin1_General_1254_BIN",
    "SQL_Latin1_General_Cp1254_CS_AS_KI_WI",
    "SQL_Latin1_General_Cp1254_CI_AS_KI_WI",
    "SQL_Latin1_General_1255_BIN",
    "SQL_Latin1_General_Cp1255_CS_AS_KI_WI",
    "SQL_Latin1_General_Cp1255_CI_AS_KI_WI",
    "SQL_Latin1_General_1256_BIN",
    "SQL_Latin1_General_Cp1256_CS_AS_KI_WI",
    "SQL_Latin1_General_Cp1256_CI_AS_KI_WI",
    "SQL_Latin1_General_1257_BIN",
    "SQL_Latin1_General_Cp1257_CS_AS_KI_WI",
    "SQL_Latin1_General_Cp1257_CI_AS_KI_WI",
    "SQL_Estonian_Cp1257_CS_AS_KI_WI",
    "SQL_Estonian_Cp1257_CI_AS_KI_WI",
    "SQL_Latvian_Cp1257_CS_AS_KI_WI",
    "SQL_Latvian_Cp1257_CI_AS_KI_WI",
    "SQL_Lithuanian_Cp1257_CS_AS_KI_WI",
    "SQL_Lithuanian_Cp1257_CI_AS_KI_WI",
    "SQL_Danish_Pref_Cp1_CI_AS_KI_WI",
    "SQL_SwedishPhone_Pref_Cp1_CI_AS_KI_WI",
    "SQL_SwedishStd_Pref_Cp1_CI_AS_KI_WI",
    "SQL_Icelandic_Pref_Cp1_CI_AS_KI_WI")

    if ($Filter){
      return $Collations | where { $_ -like "*$($Filter)*"}
    }
    else {
      return $Collations
    }
  }

function Update-Collation {
  param (
    [Parameter(Mandatory=$true)]
    [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection,
    [Parameter(Mandatory=$true)]
    [string]$Database,
    [Parameter(Mandatory=$true)]
    [string]$Collation,
    [System.Collections.Generic.List[String]]$Tables
  )
  $TableList = @()
  if ($Tables){
    $TableList =  ($SqlConnection.Databases[$Database].Tables | Where-Object {$_.Name -in $Tables})
  }
  else {
    $TableList =  ($SqlConnection.Databases[$Database].Tables)
  }

  foreach ($Table in $TableList){
    foreach ($Column in $Table.Columns){
      Write-Debug "
        Server: $($SqlConnection.Name)
        Database: $($Database)
        Table: $($Table.Name)
        Column: $($Column.Name)
        Source Collation: $($Column.Collation)
        Destination Collation: $($Collation)"

      $Column.Collation = $Collation
      try {
        $Column.Alter()
      }
      catch [System.Management.Automation.MethodInvocationException] {
        if ($Error[0].Exception -like "*Alter failed for Column*$Collation*"){
          Write-Output "$($Database).$($Table.Name).$($Column.Name) cannot be altered to $Collation, please perform this manually."
        }
      }
      catch {
        $Error[0] | select * | fl
      }
    }
  }
}


$SqlInstance = ""
$DatabaseName = ""
$Password = ''
$Username = ''
$Tables = "", ""
$InsecureCredential = Get-InsecureCredential -Username $Username -Password $Password
$Collation = Get-SqlServerCollations -Filter "Cp437_CS_A"
$ErrorActionPreference="Stop"
$Server = Connect-DbaInstance -SqlInstance $SqlInstance -Credential $InsecureCredential
Update-Collation -SqlConnection $Server -Database $DatabaseName -Collation $Collation