Updating Extended Properties of a Database using SQL Server SMO
Updating the extended properties on a database using SQL Server’s excellent Server Management Objects API is not as straightforward as setting the value and calling update.
The database.Alter() method needs to be called both before and after updating the value. I had to lookup the code of El Pluto’s awesome SQL Server Extended Properties Quick Editor project on CodePlex to figure this out.
using Microsoft.SqlServer.Management.Smo;
/// <summary>
/// Set's the extended property of a database.
/// </summary>
/// <param name="serverName">The name of the SQL Server.</param>
/// <param name="databaseName">The name of the database.</param>
/// <param name="propertyName">The name of the extended property.</param>
/// <param name="value">The value of the extended property.</param>
private void SetExtendedProperty(string serverName, string
databaseName, string propertyName, string value)
{
var server = new Server(serverName);
var database = server.Databases[databaseName];
database.Alter();
if (!database.ExtendedProperties.Contains(propertyName))
{
database.ExtendedProperties.Add(
new ExtendedProperty(database, propertyName, value));
}
else
{
database.ExtendedProperties[propertyName].Value = value;
}
database.Alter();
}