Using SQL and Powershell to set Extended Properties

I searched around for a bit and didn’t find this anywhere easy so I banged it out myself.  Hope this helps somebody to set an extended property on one of your SQL Objects using Powershell.

Assume you want to set a property on a database. If you don’t have your SQL psdrive loaded you can add it (assuming you have the prereqs) with:

add-pssnapin SqlServerProviderSnapin100

The name of your property will be “TestName” and you want a value of “TestValue”.

You will need your DB as an object.  If you have your SQL PSDrive you can use:

$DBObject = Get-Item SQLSERVER:\SQL\SERVERNAME\INSTANCE\Databases\$DBName

First, you create your new ExtendedProperty Object: 

$ExtProp = new-object Microsoft.SqlServer.Management.Smo.ExtendedProperty

Then you need to designate what object you are attaching your new property to:

$ExtProp.Parent = $DBObject

Set your Name and Value:

$ExtProp.Name = "TestName"

$ExtProp.Value = "TestValue"

Finally, create the object.  This seems to be more like applying it to me:

$ExtProp.Create()

Full list:

$DBObject = Get-Item SQLSERVER:\SQL\SERVERNAME\INSTANCE\Databases\$DBName
$ExtProp = new-object Microsoft.SqlServer.Management.Smo.ExtendedProperty

$ExtProp.Parent = $DBObject

$ExtProp.Name = "TestName"

$ExtProp.Value = "TestValue"

$ExtProp.Create()