Managing Metadata in SQL Server 2005

Today I am going to open one more category of post in my blog, which I term as "Metadata Intelligence". When working on a BI project, metadata management is one of the areas that is given the least attention, which causes a havoc in a majority of cases is the later phases of the project. Project artifacts like data dictionary, mapping documents, data lineage, audit trail and logging, etc... are given attention only when a chaos gets created for a delivery date. This has been my experience and needs not be true in a generic theory.

In the database engine, SQL 2005 expands on SQL 2000 by adding support for extended properties to nearly all database objects. Extended properties can be used for rich metadata management because each table and column (as well as most other objects in the relational engine) can have multiple extended properties that can each contain up to 7500 bytes of data. Since these properties are scriptable and are part of the structure of each object, developers can make use of these properties to store business-level definitions of each object.

Extended properties can be used for the following:

• Specifying a caption for a table, view, or column. Applications can then use the same caption in a user interface that displays information from that table, view, or column.
• Specifying an input mask for a column so that applications can validate data before running a Transact-SQL statement. For example, the required format for a postal code or telephone number column can be specified in the extended property.
• Specifying formatting rules for displaying the data in a column.
• Recording a description of specific database objects that applications can display to users. For example, the descriptions may be used in a data dictionary application or report.
• Specifying the size and window location at which a column should be displayed.
  

Extended properties is one of the starting block for starting creation of metadata management within the enterprise or system, but in a relational OLTP system. There are much more resources and tools to help or cater the same in a Business Intelligence solution (which I would discuss in future posts)

Reference: MSDN BOL