Using extended properties

SchemaExplorer gives you a great deal of information about the objects in your database. Some of the most useful information is in the ExtendedProperties collection of each object, which holds the extended properties that the database defines for it.

Reading an extended property

For example, SQL Server tells you whether a column is an identity column. You can read this with the CS_IsIdentity extended property key. The ExtendedProperties collection is indexed by key, and each item's Value holds the property's value:

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

Using ExtendedPropertyNames and GetByKey

A better way is to use the SchemaExplorer.ExtendedPropertyNames class together with the GetByKey extension method:

• ExtendedPropertyNames contains string constants for every extended property that the schema providers define, so you get IntelliSense for key names and compile-time checking.
• GetByKey<T> retrieves an extended property value and converts it to the type you ask for.

The extension methods are in the SchemaExplorer.Extensions namespace, so import it first:

<%@ Import Namespace="SchemaExplorer.Extensions" %>

Here's the identity example rewritten:

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 columns

Extended property key	ExtendedPropertyNames field	Description
CS_Description	Description	The description
CS_IsRowGuidCol	IsRowGuidColumn	The column is a row GUID column
CS_IsIdentity	IsIdentity	The column is an identity column
CS_IsComputed	IsComputed	The column is computed
CS_IsDeterministic	IsDeterministic	The column is deterministic
CS_IdentitySeed	IdentitySeed	The identity seed
CS_IdentityIncrement	IdentityIncrement	The identity increment
CS_SystemType	SystemType	The .NET system type, for example System.String
CS_Default	DefaultValue	The default value

View columns

Extended property key	ExtendedPropertyNames field	Description
CS_Description	Description	The description
CS_IsComputed	IsComputed	The column is computed
CS_IsDeterministic	IsDeterministic	The column is deterministic

Command parameters

Extended property key	ExtendedPropertyNames field	Description
CS_Description	Description	The description
CS_Default	DefaultValue	The default value

All objects

Every object has a CS_Description extended property (ExtendedPropertyNames.Description). The object's standard Description property is a shortcut to the same information.

For the extended properties that describe SQL functions, see Choosing objects.

Managing extended properties in Generator

You can view and edit extended properties in Schema Explorer.

Adding extended properties with T-SQL

You can also add your own extended properties to a SQL Server database with the sp_addextendedproperty stored procedure. For example, this statement adds a caption property to the id column of the Customers table:

EXEC sp_addextendedproperty 'caption', 'Customer ID', 'schema', dbo, 'table', Customers, 'column', id

After you run it, the caption property appears in the column's ExtendedProperties collection in Generator.