Have you ever wanted to attach attributes to a SQL Server database without needing to maintain separate meta-data tables? For example, what if you wanted to version your database? Is there a way to do this without having to create and maintain your own tables? There is indeed a way, and the way is via extended properties.

SQL Server Extended Properties: An Often Overlooked Gem

SQL Server extended properties have actually been around for a long time. And when you look at how SQL Server manages its core meta data, the fact that a facility exists to manage your own meta data shouldn’t be all that surprising. Before diving too far into the examples, let’s take a quick inventory of the tools used to illustrate the concepts:

• Visual Studio 2010
• SQL Server 2008 (any version of SQL Server will work)
• NUnit Test Framework (v. 2.5.8, download at nunit.org)

With our tools in place, let’s focus on what SQL Server gives us for free. All extended properties are stored in the sys.extended_properties table. The sys.extended_properties table contains the following columns:

• class (tinyint): Examples are database: 0, column: 1, parameter: 2 - default is 0.
• class_desc (nvarchar(60): Examples are DATABASE, OBJECT_OR_COLUMN, PARAMETER - default is DATABASE.
• major_id (int): ID of the object to which the property applies - default is 0.
• minor_id (int): Secondary ID of the object to which the property applies - default is 0. When major_id is 0, minor_id is 0.
• name (sysname: nvarchar(128)): Unique name for property.
• value (sql_variant): Value of the property.

In many cases, the only data elements you need to be concerned with are name and value. As with all system meta-data tables, the SQL login you use must have the necessary select and execute privileges. Like other system meta-data tables, you don’t need to interact with the table directly. There are several system stored procedures that handle adding, removing and updating extended properties:

• sp_addextendedproperty
• sp_updateextendedproperty
• sp_removeextendedproperty

When you work with these stored procedures, you don’t have to worry about data defaults and data validation because the stored procedures do all of the work for you.

Listing 1 and Figure 1 illustrate the T-SQL code and query results that create, update, query and remove an extended property.

Figure 1: Query results from T-SQL code in Listing 1.

Accessing SQL Server Extended Properties from Visual Studio

Being application developers that use Visual Studio, we need a way to access and work with these extended database properties from our applications. In order to demonstrate how we can access these extended properties, the following code examples use a class library project that hosts a test fixture.

If you need to engage in this sort of work, you will likely want to make your application configurable with the current database property values. For example, if your application is configured to work against version 2.0 and the database is stamped as version 1.0, you will likely want to throw an exception. The last thing you will want to do is hard code the database version into your application. Fortunately, we have the System.Configuration class that can be used to configure your application. In this case, the application is a simple test fixture that contains a few tests. In order to make the application configurable, we need the following:

• app.config file with necessary configuration section
• configuration class

The app.config file handles two things:

• Defines the configuration section (in this case, the section is named SQLExtendedProperties)
• Creates the relationship between the configuration section and the configuration class

If you are new to system configurations, check out ASP MVC Team Member Phil Haack’s blog post on the topic: http://haacked.com/archive/2007/03/12/custom-configuration-sections-in-3-easy-steps.aspx.

In this example, the application is configured to work with version 1.0.0. Also note that in the configuration, OLE-DB connection string information is associated with the version property. If you are thinking that we could have different configuration sections for test, debug and production, you are 100% correct and on the right track!

The SQLExtendedProperties class provides a structured class around the SQLExtendedProperties configuration section. Listing 4 illustrates four helper methods that are used to add, update, query and remove an extended property.

The code outlined in Listing 4 is nothing more than wrapping around ADO.NET and our application configuration. The application configuration is used to drive which database server is used. In addition, the application configuration specifies which database version is appropriate to interact with our application. Listing 5 puts it all together in a series of unit and integration tests that verifies the SQL Server Extended Property Functionality.

Conclusion

The big take away from this article is that before you go down the road of rolling your own functionality, take a moment to see if there is native functionality you can use. In the case of SQL Server meta data, you don’t necessarily need to create your own tables to persist your properties. Nevertheless, it may be that you will have to create your own tables. It depends on your requirements. Whenever possible, especially when it comes to interacting with databases, make your application configurable. It is almost never a good idea to hard code data into your applications. When you do, and if you need to modify that information, it will necessarily mean a new build will have to be pushed to production. Depending on your build and production environment, this could be a lengthy and expensive process.

USE test;
GO

declare @DBVersion as sql_variant;
select @DBVersion as nonExistentBeforeCreationVersion

EXEC         sys.sp_addextendedproperty
@name = N'Version',
@value = N'1.0.0';

select @DBVersion = value
   from sys.extended_properties
   where name = 'Version';

select @DBVersion as newVersion

EXEC sp_updateextendedproperty
    @name = N'Version'
    ,@value = '2.0.0';

Set @DBVersion = null;   

select @DBVersion = value
   from sys.extended_properties
   where name = 'Version';

select @DBVersion as updatedVersion
   
EXEC sp_dropextendedproperty
     @name = 'Version';
   
Set @DBVersion = null;

select @DBVersion = value
   from sys.extended_properties
   where name = 'Version';

select @DBVersion as nonExistentAfterDropVersion

<?xml version="1.0" encoding="utf-8" ?>
<configuration>

  <configSections>
    <section name="SQLExtendedProperties"
             type="DBExtendedProperties.SQLExtendedProperties, 
               DBExtendedProperties"/>
  </configSections>

  <SQLExtendedProperties
     connection = "Integrated Security=SSPI;Initial Catalog=test;Data Source=."
     version = "1.0.0"  
  />

</configuration>

using System;
using System.Configuration;

namespace DBExtendedProperties
{
 public class SQLExtendedProperties : ConfigurationSection
 {
 public static SQLExtendedProperties LoadBySectionName()
 {
 Return
 ConfigurationManager.GetSection("SQLExtendedProperties")
  as SQLExtendedProperties;

[ConfigurationProperty("connection", IsRequired = true)]
 public string connection
 {
  get { return (string)this["connection"]; }
  set { this["connection"] = value; }
 }

  [ConfigurationProperty("version", IsRequired = true)]
 public string version
 {
 get { return (string)this["version"]; }
 set { this["version"] = value; }
 }
}
}

private static void DropExtendedProperty(SqlConnection _connection,
 string PropertyName)
 {
using 
(var _command = 
  new SqlCommand("sys.sp_dropextendedproperty",_connection))
 {
 _command.CommandType = CommandType.StoredProcedure;
 SqlCommandBuilder.DeriveParameters(_command);
 _command.Parameters["@name"].Value = PropertyName;
 _command.ExecuteNonQuery();
}
}


private static string GetExtendedPropertyValue(SqlConnection
 _connection, string PropertyName)
{
 string _version;
using 
(var _command =
 new SqlCommand(String.Format("select value from
      sys.extended_properties where name = '{0}'", PropertyName),
   _  connection))
{
 var _reader = _command.ExecuteReader();
 _reader.Read();
 _version = _reader.GetString(0);
 _reader.Close();
 return _version;
}
}


private static void AddExtendedProperty(SqlConnection _connection,
 string PropertyName)
{
var _command = new SqlCommand("sys.sp_addextendedproperty",
 _connection);
 _command.CommandType = CommandType.StoredProcedure;

SqlCommandBuilder.DeriveParameters(_command);

 _command.Parameters["@name"].Value = PropertyName;
 _command.Parameters["@value"].Value =
 SQLExtendedProperties.LoadBySectionName().version;

 _command.ExecuteNonQuery();

}


private static void UpdateExtendedProperty(SqlConnection
 connection, string PropertyName, string PropertyValue)
 {
 using (var _command = new
 SqlCommand("sys.sp_updateextendedproperty", _connection))
{
 _command.CommandType = CommandType.StoredProcedure;
 SqlCommandBuilder.DeriveParameters(_command);
 _command.Parameters["@name"].Value = PropertyName;
 _command.Parameters["@value"].Value = PropertyValue;
 _command.ExecuteNonQuery();
}

} 

private static SqlConnection GetConnection()
 {
var _connection = new
SqlConnection(SQLExtendedProperties.LoadBySectionName().connection);
 _connection.Open();
 return _connection;
}

 [Test]
public void can_retrieve_version_value_from_config_file()
{
  var _version = SQLExtendedProperties.LoadBySectionName().version;
  Assert.AreEqual("1.0.0", _version);
}

 [Test]
public void can_open_and_close_db_connection()
{
  var _connection = GetConnection();
  Assert.AreEqual(ConnectionState.Open, _connection.State);

  _connection.Close();
  Assert.AreEqual(ConnectionState.Closed, _connection.State);
 }

 [Test]
public void can_create_extended_property()
{
  var _connection = GetConnection();
  AddExtendedProperty(_connection, "Version");

  Assert.AreEqual(SQLExtendedProperties.LoadBySectionName().version,
   GetExtendedPropertyValue(_connection,"Version"));

  DropExtendedProperty(_connection,"Version");

  _connection.Close();

}

 [Test]
public void can_update_extended_property()
{
 var _connection = GetConnection();

 AddExtendedProperty(_connection, "Version");

 UpdateExtendedProperty(_connection, "Version","2.0.0");

 Assert.AreEqual("2.0.0", GetExtendedPropertyValue(_connection, 
  "Version"));

DropExtendedProperty(_connection, "Version");

_connection.Close();

}