Extended Properties...

Extended properties are a new database feature with the SQL Server 2005 product. They store additional information for a wide array of database objects...

By: Brian Mains Spacer Date: March 3, 2006Spacer Download Spacer Download the code.

Extended properties are a new database feature with the SQL Server 2005 product. Extended properties store additional information for a wide array of database objects, such as tables or columns. For example, you could store table and column descriptions of purpose and business information directly in the database for that object t. Extended properties can store a dynamic amount of properties as they aren’t constrained to just one or a few. That being said, we can store whatever we want directly in the database about an object.

To do this in SQL Server directly, you use the sp_addextendedproperty stored procedure, which is documented at: http://msdn2.microsoft.com/en-us/library/ms180047.aspx and http://msdn2.microsoft.com/en-us/library/ms190243.aspx. But this example isn’t worrying about the SQL Server side way of doing things; rather, I’m going to focus on using it in code.

Working off of a theme before, I’m using a listbox to show all of the available columns for all of the databases. When selecting a column in the listbox, it shows all of the existing extended properties (more than one selected shows only the first column’s information). Through two textboxes, the form will allow the creation of extended properties with whatever name and value you desire. Also, you can add extended properties to as many columns as you want, by selecting multiple items in the side listbox. So, let’s look at the code. On page load, the web form loops through the databases, tables, and columns to create a string in the form of: master.sysoptions.optname.

server = new Server(conn);
string path = "{0}.{1}.{2}";

if (!Page.IsPostBack)
{
  foreach (Database db in server.Databases)
  {
    foreach (SMO.Table tb in db.Tables)
    {
      foreach (Column col in tb.Columns)
      {
        string val = string.Format(path, db.Name, tb.Name, col.Name);
        lbColumns.Items.Add(new ListItem(val, val));
      }
    }
  }
}

When all of the information is needed to retrace our steps, the string concatenation is split apart by using the string.Split method to break it up into pieces around the period. This is a great delimiter to use to get back to the original name.

private Column GetColumn(string text)
{
  string[] names = text.Split('.');
  string db = names[0];
  string tb = names[1];
  string col = names[2];

  Database smoDB = server.Databases[db];
  SMO.Table smoTB = smoDB.Tables[tb];
  Column smoCOL = smoTB.Columns[col];
  return smoCOL;
}

Whenever the list box selection changes, the page posts back through autopostback and the current column is queried, determining whether it has extended properties. If not, is shows "No Properties." Also, whenever multiple items are selected, only the first one is shown with its properties (rendered directly to a label).

protected void lbColumns_SelectedIndexChanged(object sender, EventArgs e)
{
  lblExtendedProperties.Text = string.Empty;
  if (lbColumns.SelectedValue == null)
    return;

  Column col = GetColumn(lbColumns.SelectedValue);
  foreach (ExtendedProperty prop in col.ExtendedProperties)
  {
    lblExtendedProperties.Text += prop.Name + ": " + prop.Value + "<br>";
  }

  if (lblExtendedProperties.Text == string.Empty)
    lblExtendedProperties.Text = "No Properties.";
}

A small form below the rendered properties allows the creation of an extended property for a single or multiple column(s). Based upon the selection, the list is iterated through, checking if the item is selected. If it is, the column reference is retrieved, and if the column doesn't already have the extended property you are trying to add, it creates a new one. This occurs through the prop.Create() statement:

protected void lnkAdd_Click(object sender, EventArgs e)
{
  foreach (ListItem item in lbColumns.Items)
  {
    if (item.Selected)
    {
      Column col = GetColumn(item.Value);
      if (!col.ExtendedProperties.Contains(txtPropName.Text))
      {
        ExtendedProperty prop = new ExtendedProperty(col, txtPropName.Text, txtPropValue.Text);
        prop.Create();
      }
    }
  }

  txtPropName.Text = string.Empty;
  txtPropValue.Text = string.Empty;
}

One important note is how SMO works with creating (and other modifications) objects. The object itself, not its collection, performs the actual modification. Calling prop.Create() creates the extended property for the database. There is no need to add it to the collection, because it will already exist (I already tried with error). It knows this because the constructor takes the reference to the SMO object that it is being created for. This is a change from the approach that objects usually work, but I actually like the implementation used. The example attached performs these operations but note that it takes a while to load the initial time, because of the volume of data.