try{ $ErrorActionPreference = "Stop" [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null $watch = [system.diagnostics.stopwatch]::StartNew() #location of the output files Set-Location "C:\PowershellScripts" $alter_column_file = "alter_column.sql" $create_index_file = "create_index.sql" $drop_index_file = "drop_index.sql" $tables_to_alter_file = "tables_to_alter.txt" Set-Content $alter_column_file "--alter column" Set-Content $create_index_file "--create index" Set-Content $drop_index_file "--drop index" Set-Content $tables_to_alter_file "" $server = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'YOUR_SERVER_NAME' if($server.Databases.Count -le 0){Write-Host -ForegroundColor Yellow "There are no databases. Check the server name."; Exit} $db = $server.Databases["your_db_name"] if($db.Tables.Count -eq 0){Write-host -ForegroundColor Yellow "There are no tables in this db. Check the db name"; Exit} $default_collation = $db.Collation $sb_alter_column = New-Object System.Text.StringBuilder $sb_create_index = New-Object System.Text.StringBuilder $sb_drop_index = New-Object System.Text.StringBuilder $scrp = New-Object Microsoft.SqlServer.Management.Smo.Scripter($server) $tables = $db.Tables [System.Collections.ArrayList] $tables_to_alter = @() $initfields = $server.GetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Column]) $initfields.Add("Collation") $initfields.Add("DataType") $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Column], $initfields) foreach($table in $tables){ foreach ($column in $table.Columns){ if($column.Collation -and $column.Collation -ne $default_collation){ $sb_alter_column.Append("ALTER TABLE ") | Out-Null $sb_alter_column.Append($table.Name) | Out-Null $sb_alter_column.Append(" ALTER COLUMN ") | Out-Null $sb_alter_column.Append($column.Name) | Out-Null $sb_alter_column.Append(" ") | Out-Null $sb_alter_column.Append($column.DataType) | Out-Null $sb_alter_column.Append("(") | Out-Null $sb_alter_column.Append($column.DataType.MaximumLength) | Out-Null $sb_alter_column.Append(")") | Out-Null $sb_alter_column.Append(" COLLATE ") | Out-Null $sb_alter_column.Append($default_collation) | Out-Null $sb_alter_column.Append(" NOT NULL") | Out-Null $sb_alter_column.Append("`r`n GO `r`n") | Out-Null if(!$tables_to_alter.Contains($column.Parent.Name)){ $tables_to_alter.Add($column.Parent.Name) | Out-Null } } } } $sb_alter_column.ToString() | Add-Content $alter_column_file $sb_alter_column.Length = 0 $tables_to_alter | Add-Content $tables_to_alter_file $tables_to_alter.Clear() foreach($tb in Get-Content $tables_to_alter_file | Select-Object -Skip 1){ $currenttable = $db.Tables.Item($tb) foreach ($index in $currenttable.Indexes){ #drop indexes and PK 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 PK 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 Write-Host $watch.Elapsed.Hours : $watch.Elapsed.Minutes : $watch.Elapsed.Seconds } catch{ Write-Host -ForegroundColor Magenta $Error[0].Exception while($Error[0].InnerException){ Write-Host $Error[0].InnerException.Message } }