Script and Alter SQL Server Database Objects with PowerShell to Correct Collation Issues

-- tables affected

SELECT DISTINCT TABLE_NAME
FROM [db_name].[INFORMATION_SCHEMA].[COLUMNS]
WHERE COLLATION_NAME = 'Latin1_General_100_BIN2'

SELECT DISTINCT o.name AS table_name
FROM [db_name].[sys].[objects] o 
JOIN [db_name].[sys].[columns] c ON o.object_id = c.object_id
WHERE o.type = 'U' AND c.collation_name = 'Latin1_General_100_BIN2'

-- columns affected

SELECT COLUMN_NAME
FROM [db_name].[INFORMATION_SCHEMA].[COLUMNS]
WHERE COLLATION_NAME = 'Latin1_General_100_BIN2'

SELECT c.name AS column_name
FROM [db_name].[sys].columns c
WHERE c.collation_name = 'Latin1_General_100_BIN2'

ALTER TABLE  ALTER COLUMN   COLLATE Latin1_General_BIN

Msg 5074, Level 16, State 1, Line 1
The index  is dependent on column .
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Comments failed because one or more objects access this column.

    try{
    $ErrorActionPreference = "Stop"
  
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
    Import-Module SQLPS -DisableNameChecking
    
    $loc = "C:\PowershellScripts"
    Set-Location $loc;
    #location of the output files
    $alter_column_file = "alter_column_statements.sql"
    $create_index_file = "create_index.sql"
    $drop_index_file = "drop_index.sql"
    $table_source = "bad_tables.txt"
    #location of the input files - TSQL scripts I'm using to obtain a list of the tables and columns affected
    $bad_tables_tsql = "bad_tables.sql"
    $alter_column_tsql = "alter_column.sql"
    
    #initial content of the output files
    Set-Content $alter_column_file "--alter column"
    Set-Content $create_index_file "--create index"
    Set-Content $drop_index_file "--drop index"
    Set-Content $table_source ""
    
    #setting up the SQL context
    $server = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer' 
    if($server.Databases.Count -le 0){Write-Host -ForegroundColor Yellow "There are no databases. Check the server name."; Exit}
    $database = $server.Databases["db_name"]
    if($database.Tables.Count -eq 0){Write-host -ForegroundColor Yellow "There are no tables in this db. Check the db name"; Exit}
   
    #scripter object I'll use later to script the inexes for each table
    $scrp = New-Object Microsoft.SqlServer.Management.Smo.Scripter($server)
                
    $sb_create_index = New-Object System.Text.StringBuilder
    $sb_drop_index = New-Object System.Text.StringBuilder
    
    #column list
    $sqlcheck = Invoke-sqlcmd -InputFile $alter_column_tsql
    if($sqlcheck -eq $null){Write-Host -ForegroundColor Green "There are no columns to alter"; Exit}
    $sqlcheck | Out-File $alter_column_file
    #tables list
    Invoke-Sqlcmd -InputFile $bad_tables_tsql | Out-File -filePath $table_source
    
    #scripting the indexes for the "damaged" tables
    foreach($table in Get-Content $table_source | Select-Object -Skip 3){
        $currenttable = $database.Tables.Item($table.Trim())
        #Write-Host $currenttable
       foreach($index in $currenttable.Indexes){
           #drop indexes and constraints
           $scrp.Options.IncludeIfNotExists=$True
           $scrp.Options.ScriptDrops = $True
           foreach ($sd in $scrp.Script($index)){
                $sb_drop_index.Append($sd + "`r`n GO `r`n") | Out-Null
            }
           #create indexes and constraints again
           $scrp.Options.IncludeIfNotExists=$True
           $scrp.Options.ScriptDrops = $False
           foreach ($sc in $scrp.Script($index)){
                $sb_create_index.Append($sc + "`r`n GO `r`n") | Out-Null
            }
        }
    }
    
    $sb_create_index.ToString() | Add-Content $create_index_file
    $sb_drop_index.ToString() | Add-Content $drop_index_file

}
catch{
    Write-Host -ForegroundColor Magenta $Error[0].Exception
    while($Error[0].InnerException){
        Write-Host $Error[0].InnerException.Message
    }
   }
 

-- tables
SELECT DISTINCT TABLE_NAME
FROM [db_name].[INFORMATION_SCHEMA].[COLUMNS]
WHERE COLLATION_NAME = 'Latin1_General_100_BIN2'

-- columns
-- It's mandatory to specify the column data type.
-- The ERPLN database does not use nullable columns; therefore I've specified NOT NULL in each statement to preserve the column's nullability.
SELECT
'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE  +'(' + CAST(CHARACTER_MAXIMUM_LENGTH AS varchar(5)) + ')' + ' COLLATE Latin1_General_BIN' + ' NOT NULL'
FROM [db_name].[INFORMATION_SCHEMA].[COLUMNS]
WHERE COLLATION_NAME = 'Latin1_General_100_BIN2'

-- $table_source - bad_tables.txt
-- noice the header, that's the reason for the Select-Object -Skip 3 condition
TABLE_NAME                                                                                                                                                                  
----------                                                                                                                                                                  
ttlanl100000                                                                                                                                                                
ttlanl103000                                                                                                                                                                
ttlbct350000
.
.
.

--$alter_column_file - alter_column_statements.sql
Column1                                                                                                                                                                     
-------                                                                                                                                                                     
ALTER TABLE tttspt101000 ALTER COLUMN t_user varchar(12) COLLATE Latin1_General_BIN NOT NULL                                                                               
ALTER TABLE tttspt101000 ALTER COLUMN t_pacc varchar(8) COLLATE Latin1_General_BIN NOT NULL                                                                                
ALTER TABLE tttspt101000 ALTER COLUMN t_comp varchar(10) COLLATE Latin1_General_BIN NOT NULL 
.
.
.