Quick post on how to add a column to a table via powershell. I had a need to blast a new column onto a bunch of databases on a bunch of servers. Powershell makes this too easy. Use at your own risk.
[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 $tableName = 'TableName' $schema = 'dbo' $columnName = 'ColumnName' try{ $servers | %{ $srvName = $_ $srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection" $srvConn.ServerInstance = $srvName $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn $srv.Databases | where{$_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal} | %{ $dbName = $_.Name $table = $_.Tables | where{$_.Name -eq $tableName -and $_.Schema -eq $schema} if($table -ne $null){ $column = $table.Columns | where{$_.Name -eq $columnName} if($column -eq $null){ #add the column if it does not exist $column = New-Object Microsoft.SqlServer.Management.Smo.Column $column.Parent = $table $column.Name = $columnName $column.DataType = [Microsoft.SqlServer.Management.Smo.DataType]::DateTime $column.Create(); } } } } } catch{ $_ | fl -Force }