#PSTip Updating extended properties on database objects using SMO

Note: This tip requires PowerShell 2.0 or above.

In an earlier tip, we saw how we can read extended properties to a database. In this tip we see how we can alter them.

We can use extended properties to keep a record of the configuration changes made to the DB. Assuming we already have a set of extended properties called ‘Change made’ and ‘Change made by’, let us see how we can update these properties.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME
$server.Databases["sqlchow"].ExtendedProperties | Select Name, Value, State

Name          Value                               State
----          -----                               -----
Change made   Recovery Model set to BulkLogged Existing
Change madeby CandidConvos                     Existing

$proptochange  = $server.Databases["sqlchow"].ExtendedProperties["Change made by"]
$proptochange.Value = "Crack-Monkey"
$proptochange.Alter()
$server.Databases["sqlchow"].ExtendedProperties | Select Name, Value, State

Name          Value                               State
----          -----                               -----
Change made   Recovery Model set to BulkLogged Existing
Change madeby Crack-Monkey                     Existing