Skip to content

April 22, 2014

Change Column Length via Powershell

by Scott Newman

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[‘Length’].value to whatever length you are setting.


$servers = Get-Content c:\Servers.txt

	$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
	$_ | fl -Force
Read more from Powershell, SQL Server

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments

%d bloggers like this: