Build a SQL Server Data Dictionary with the Extended Properties

By:   |   Comments (1)   |   Related: > Database Design


Problem

A data dictionary is a collection of descriptions about the data and objects in a data model for the benefit of programmers and users who need to refer to them when building the application, reports, etc.  It is ideal to create a data dictionary while working on the data model. When the database is small, developers often use built-in SQL Server tools and scripts to create the data model.  However when the data model grows into an enterprise level data model with all of the associated complexities, the management and maintenance grow as well.  So what options are available to manage and build the data model?  Are there any third party tools available?

Solution

Fortunately, most major data modeling tools that I have worked with so far use the same methods to store the Data Dictionary.  Some tools may use different terminology and may have more features, but essentially the underlying technologies are the same. The underlying technology are a set of SQL Server Extended Properties which have been available for a long time and are also supported in SQL Server 2008. There might be some changes with SQL Server 2011, but I am sure there will be an easy work around if they are not fully supported.

Disclaimer: The purpose of writing this tip is to show that most of the third party tools use Extended Properties for managing the Data Dictionary.  This tip is not intended to make a judgment call on what tool is doing a better job.  As such, I may not be able to cover all of the data model tools available so I randomly picked a few tools.

Overview - Extended Properties

For the scope of this article, I will not outline what an is Extended Property is and how to add/create/update Extended Properties in detail. There is a great article SQL Server Data Dictionary in Seconds using Extended Properties that you can read to understand how you can use Extended Properties and also you can check out Books Online for this article - Using Extended Properties on Database Objects.

Like I stated previously, when you are working with an Enterprise Data Modeling, very often you are going to use third party tools to manage the model.  Those tools have features that let you reverse engineer the data diagram and data dictionary as well as forward engineer the model back to the SQL server database.

As I stated earlier, the extended properties have been available since SQL Server 2000.  If you are using SQL Server 2000 Enterprise Manager to add an Extended Property, the name of the extended property will always be "MS_Description" in those system tables.  Reference the two images below as a point of reference.

SQL Server 2000 Table level Extended Property - See the Description area outlined.

properties

SQL 2000 Column Level - See Description

server id extended property

As you can see, there is no Name defined for those Descriptions in SQL Server 2000. However, if you are using SQL Server 2005 Management Studio or later, both a name and value are defined as show below.

SQL 2005/2008 tool Table Level

table properties

SQL 2005/2008 Tool - Column Level

column properties

As you can see, from SQL Server 2005/SQL Server 2008 Management Studio, you can add multiple extended properties per object.  One important point is that the NAME MUST BE "MS_Description" for other tools to be able to read the data.

With that being covered, let me show you how we can interacted with the Extended Properties in some third party tools. Please note that due to time and licensing limitations, I only was able to provide the following screen shots for certain products.

Computer Associates ErWin Data Modeler

Here is the screen shot of this tool showing that you can choose the option to Reverse Engineer the existing Extended Properties.

reverse engineer

And you can modify or add Table Level Extended Property on below option.

sql server table product editor

Here is the column level Extended Properties for the Data Dictionary.

sql server table individual editor

Once you are finished modeling, you can forward Engineer the model to generate a Data Dictionary by using the Extended Properties which they refer to as "Comments" in their product.

forward enginer schema generation

Below is the output for the data dictionary using the Extended Properties. As you can see, it generated code based on the sp_addextednedproperty stored procedure and the name is "MS_Description" as I mentioned above.

sql server schema generation preview

Embarcadero's ER/Studio

This tool has the same options to reverse engineer as the previous tool as show below:

table editor

Here is a screen shot of the column level Extended Properties:

table editor

Here is a screen shot when forward engineering the database with the same set of extended properties:

universal isql

Quest's Toad Data Modeler

With this tool check out the "Load Descriptions" option to access the Extended Properties as shown below:

reverse engineering wizard

Here is the an example of the table level Extended Properties:

entity properties

Here is an example of the column level Extended Properties:

entity properties

To forward engineer the database select the Create Descriptions option as shown below:

script generation

As you can see the associated wcript that was generated by Forward Engineer process uses the same Extended Properties stored procedures:

create table

Visual Studio 2005 Team Foundation System with Database Professional

With this tool you can reverse engineer the existing database and see an interface similar to the following:

microsoft visual studio

Microsoft Excel Screenshot.

With some customized T-SQL scripting, you can get this.

microsoft excel

Microsoft Visio 2007

With Microsoft Visio 2007 after you reverse engineer the database you will see an interface similar to the one below. The 'Notes' is the Extended Property Description.

microsoft visio

As I previously mentioned, the purpose of this tip is not to show specific tools, but that those tools use the Extended Properties to manage the Data Dictionary.  To take this a step further, there are other tools to generate SQL Server Documentation which generate the reports based on Extended Properties as well. So, in my opinion using the Extended Properties is a Win-Win situation.  Further, since all of these vendors support the Extended Properties I am not stuck with one tool and anyone who can write a script can participate in managing the data dictionary.

Next Steps
  • If your current data is in an Excel spreadsheet, Microsoft Access or even in a SQL Server table, you can write a few queries to forward engineer the data with the Extended Properties like the third party tools and be able to manage the data in a more standard format.
  • You can also automate existing Extended Property into Reporting Services if you want to publish to an internal portal.
  • To ensure the data dictionary is complete you can setup a daily report to find of if there is any missing data and request the developers to update the model.
  • As a best practice, it is important to build a guide to have a consistent set of documentation.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Kun Lee Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, December 6, 2018 - 10:44:19 AM - Mike Back To Top (78414)

 Would love to see an updated version of this article. ;-)















get free sql tips
agree to terms