Use Extended Properties to Create a Data Dictionary

I'm looking for shareware or an inexpensive tool that will let me maintain a SQL Server data dictionary. I've used sophisticated data-modeling tools that provide this functionality. However, I'm now in an organization that has many SQL Server databases but no data dictionary and no budget to buy expensive tools. Do you know of a poor-man's version of a data-dictionary tool?

Although a free or inexpensive shareware tool might exist, I'm not familiar with it. However, I suggest that you try using SQL Server 2000's extended properties. In SQL Server 2000, Microsoft added extended properties to let you define and manipulate user-defined properties on various database objects. You can use these user-defined properties to add metadata to your database that you can use to create a homegrown data dictionary integrated directly into SQL Server. You manage these properties by using the system stored procedures sp_addextendedproperty, sp_updateextendedproperty, and sp_dropextendedproperty. In addition, you can use the system function fn_listextendedproperty() to retrieve an existing property value. Microsoft uses extended properties to populate and manage the description value that you see associated with a column in the design view of a table in Enterprise Manager.

Note that using these stored procedures and the fn_listextendedproperty() function to create and manage extended properties isn't intuitive. The SQL Server Books Online (BOL) topic "Property Management" gives you only basic information about extended properties. Fortunately, SQL Server Magazine has published several good articles about this topic. For more information about using extended properties, see Michelle A. Poolet, Solutions by Design, "A Business Metadata Repository," October 2002, InstantDoc ID 26273; William Vaughn, "Managing Extended Properties," July 2001, InstantDoc ID 20886; and SQL Server Savvy, "Tip: Managing Data Dictionaries Based on SQL Server 2000 Extended Properties," November 2001, InstantDoc ID 22399. All three articles explain how to use extended properties, and each article provides a shell that you can use for writing your own poor-man's data dictionary.