Using extended properties to quickly create a SQL Server data dictionary

Problem

I need a way to create a dictionary that keeps up with the latest data. I made a lot of changes to the database, and I spent more time updating the database documents than the database administration time.

Expert answers

If you store metadata as an extended attribute, you can use SQL Server 2005 to create a data dictionary for a database within seconds. The SQL Server AdventureWorks sample database contains a number of extended properties, so this database is a good example. In this article, we will introduce two core elements. The first is a set of scripting examples that add extended properties to tables and fields. The next is T-SQL code that generates the HTML format data dictionary.

Sample Script--sys.sp_addextendedproperty

The following is a sample script that adds extended attributes to the database.

To add extended properties to tables and fields

/**********
The following extended properties already exist in the AdventureWorks database. There is no need to run the script against the database in order for the remaining of the samples to work.
**********/
Use [AdventureWorks]
Go
--script to add a Extended to the Table
EXEC sys.sp_add Extendedproperty
@name =n ' ms_description ',
@value =n ' street address information for customers, employees, and Vendors. ',
@level0type =n ' SCHEMA ',
@level0name =n ' person ',--schema Name
@level1type =n ' TABLE ',
@ Level1name=n ' address '--table Name
Go
--script to add, Extended property to a column
EXEC sys.sp_addextended Property
@name =n ' ms_description ',
@value =n ' The ' A ', ' ",
@level0type =n ' SCHEMA ',
@ Level0name=n ' person ',--schema name
@level1type =n ' table ',
@level1name =n ' address ',--TABLE name
@ Level2type=n ' COLUMN ',
@level2name =n ' AddressLine1 '--column Name
Go

You can also view extended properties by right-clicking an object in SSMs and selecting Properties, as shown in the following illustration:

If your database has data in the extended attribute, then you can run the query to extract the data. In the SQL Server Management suite, select Tools | Options, and do not select the "Include column headers in ' result set" (Include field headers in the results set) option in results to text. This will cause the result set displayed below each field name to contain No field headers.