By: Kun Lee | 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.
SQL 2000 Column Level - See Description
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
SQL 2005/2008 Tool - Column Level
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.
And you can modify or add Table Level Extended Property on below option.
Here is the column level Extended Properties for the Data Dictionary.
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.
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.
Embarcadero's ER/Studio
This tool has the same options to reverse engineer as the previous tool as show below:
Here is a screen shot of the column level Extended Properties:
Here is a screen shot when forward engineering the database with the same set of extended properties:
Quest's Toad Data Modeler
With this tool check out the "Load Descriptions" option to access the Extended Properties as shown below:
Here is the an example of the table level Extended Properties:
Here is an example of the column level Extended Properties:
To forward engineer the database select the Create Descriptions option as shown below:
As you can see the associated wcript that was generated by Forward Engineer process uses the same Extended Properties stored procedures:
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 Excel Screenshot.
With some customized T-SQL scripting, you can get this.
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.
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.
About the author
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
