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();

}