Add a column to a table via powershell

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
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.