#PSTip Creating extended properties on database objects using SMO

Note: This tip requires PowerShell 2.0 or above.

You might have seen several articles on the web discussing about the SQL database extended properties from a self-documentation point of view. I believe that we can use extended properties for much more than self-documentation.

Here is an example. Lets say you make a change to a DB configuration as part of a request. If you add the details and date of the change to the DB then, you exactly know what to revert when you have to do it 3-4 weeks down the line.

So, let us see how we can add extended properties using PowerShell.

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
$extproperty = New-Object Microsoft.SqlServer.Management.Smo.ExtendedProperty

$extproperty.Parent = $server.Databases['sqlchow'] #needs smo object.
$extproperty.Name = 'Change made'
$extproperty.Value = 'Recovery Model set to BulkLogged'
$extproperty.Create()

In this tip we are adding extended properties to a DB, you can add extended properties to other objects as well. For example, if you need to add extended properties to a table, the Parent is going to be:

$server.Databases['sqlchow'].Tables['userdetails']