Using Extended Properties

SchemaExplorer allows you to retrieve a great deal of information about objects within your database. If you're using a RDBMS (E.G., 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 the RDBMS defines for database objects.

Example

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 key as shown below.

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

A better way to retreive this value would be to use the SchemaExplorer.ExtendedPropertyNames utility class and ExtendedProperty Extension methods.

The SchemaExplorer.ExtendedPropertyNames class contains string constants of all Schema Provider defined extended properties. This gives you Intellisense for extended property key names as well as compile time checking! The  The below code sample has been updated to use this utility class. The example below will show off how to use Extended Properties GetByKey Extension method for retrieving and converting extended property values to the correct type.

Identity Field = <% foreach(ColumnSchema cs in SourceTable.Columns) {
    if(cs.ExtendedProperties.GetByKey<bool>(SchemaExplorer.ExtendedPropertyNames.IsIdentity) == true) {
        Response.Write(cs.Name);
    }
} %>

Default Extended Properties

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

Table Column

View Column

Command Parameter

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 Generator offers an easy way to manage Extended Properties through the Template Explorer's SchemaExplorer Control.

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 Generator.