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.