Using Extended Properties

Introduction

SchemaExplorer allows you to retrieve a great deal of information about objects within your database. If you're using a SQL Server database, you'll find some of the most useful information in the ExtendedProperties collections of the various objects. These collections contain the extended properties that SQL Server defines for database objects. For example, SQL Server defines an extended property that tells you whether a table column is an identity column, which you can retrieve with the CS_IsIdentity extended property:

Identity Field = <% foreach(ColumnSchema cs in SourceTable.Columns) { 
      if( ((bool)cs.ExtendedProperties["CS_IsIdentity"].Value) == true)
      {
            Response.Write(cs.Name);
      }
}
%>

CodeSmith defines standard extended properties for table columns, view columns, and command parameters:

Table Column:

 CS_IsRowGuidCol
 CS_IsIdentity
 CS_IsComputed
 CS_IsDeterministic
 CS_IdentitySeed
 CS_IdentityIncrement
 CS_Default

View Column:

CS_IsComputed
CS_IsDeterministic

Command Parameter:

CS_Default

In addition, every object has a CS_Description extended property, but the standard Description property provides a shortcut to the same information.

Using CodeSmith to Manage Extended Properties

CodeSmith offers an easy way to manage Extended Properties through the CodeSmith Studio's SchemaExplorer Control.

More: Using CodeSmith to Manage Extended Properties

Manually Adding Extended Properties

You can also create your own extended properties within your SQL Server database by using the sp_addextendedproperty stored procedure. For example, this T-SQL statement adds a Caption property to the ID column of the Customers table:

sp_addextendedproperty 'caption', 'Customer ID', 'user', dbo, 'table', Customers, 'column', id

After you execute this statement in your SQL Server database, the Caption property will show up in this column's ExtendedProperties collection in CodeSmith.