#PSTip Deleting extended properties on database objects using SMO

Note: This tip requires PowerShell 2.0 or above.

In earlier tips, we looked at how to add, read and update the extended properties of a SQL database. In this tip we will see how we can delete them, in-case we need to do some cleanup.

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   Set recovery model to simple Existing
Change madeby Crack-Monkey                 Existing

#drop the extended property
$server.Databases["sqlchow"].ExtendedProperties["Change made"].Drop()
$server.Databases["sqlchow"].ExtendedProperties | Select Name, Value, State

Name          Value           State
----          -----           -----
Change madeby Crack-Monkey Existing

About the author: Hemanth C Damecharla

Hemanth is a SQL*Server DBA and a PowerShell enthusiast who loves to keep looking for new things to tinker around with. He maintains that he can write poetry. He blogs at http://sqlchow.wordpress.com and you can follow him on Twitter: @sqlchow

Related Posts

%d bloggers like this: