Changing the length of a column is not as straightforward as you would think. There is no .Length property on the smo column object itself, rather you have to set the column.property[‘Length’].value to whatever length you are setting.
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$servers = Get-Content c:\Servers.txt
try{
$servers | %{
$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
$srvConn.ServerInstance = $_
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
$srv.databases | where{$_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal} | %{
$dbName = $_.Name
$_.Tables | where{$_.Name -eq "TableName" -and $_.Schema -eq "dbo"} | %{
$tableName = $_.Name
$_.Columns | where{$_.Name -eq 'ColumnName'} | %{
if([int]$_.Properties['Length'].value -eq 100){
$_.Properties['Length'].value = 500
$_.Alter();
}
}
}
}
}
}
catch{
$_ | fl -Force
}
Sql Server Powershell 