Saturday, July 11, 2009

Using PowerShell and SMO to change database columns collations

Changing a SQL serve database columns collations manually can be a tedious task. I have a database that I want to change the collation of all its non system columns to "Arabic_CS_AS".
Here is a PowerShell script that uses SQL Server Management Objects (SMO) to do this task:
(note that I load the assemblies with version 10.0.0 which is the version of SQL server 2008 I have installed on my system)

[System.Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
[System.Reflection.Assembly]::Load("Microsoft.SqlServer.ConnectionInfo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

$con = New-Object Microsoft.SqlServer.Management.Common.ServerConnection

$con.ConnectionString="Data Source=.\SQLExpress;Integrated Security=SSPI;"


$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $con
$db = $srv.Databases["test"]

foreach ($table in $db.Tables)

foreach($column in $table.Columns)
if(-not ([string]::IsNullOrEmpty($column.Collation)))
$column.Collation = "Arabic_CS_AS"


No comments: