Are there any inexpensive Microsoft or 3rd party tools that anyone could recommend
forcreating a data dictionary?

Thanks.

Brian

Brian,

Here is

AdventureWorks Data Dictionary


You can add EXTENDED PROPERTY as description for each table, column, etc. That is where the above link is getting the descriptions from.

You can create instant dictionary by taking the extendend properties system view and combine it with INFORMATION_SCHEMA views. Example on querying extended properties.

Minor_id 0 is the table, the rest of them are the columns by ordinal numbers.

SELECT   ObjectName = isnull(OBJECT_NAME(major_id),''), 
         minor_id, 
         VALUE, 
         class_desc, 
         major_id 
FROM     sys.extended_properties 
ORDER BY Class_desc, 
         ObjectName, 
         minor_id 

/* Partial listing
ObjectName	minor_id	value	class_desc	major_id
ProductPhoto	0	Product images.	OBJECT_OR_COLUMN	274100017
ProductPhoto	1	Primary key for ProductPhoto records.	OBJECT_OR_COLUMN	274100017
ProductPhoto	2	Small image of the product.	OBJECT_OR_COLUMN	274100017
ProductPhoto	3	Small image file name.	OBJECT_OR_COLUMN	274100017
ProductPhoto	4	Large image of the product.	OBJECT_OR_COLUMN	274100017
ProductPhoto	5	Large image file name.	OBJECT_OR_COLUMN	274100017
ProductPhoto	6	Date and time the record was last updated.	OBJECT_OR_COLUMN	274100017
*/

The following link explain how to document database objects using sp_addextendedproperty :

Using Extended Properties on Database Objects
http://msdn.microsoft.com/en-us/library/ms190243.aspx


Html data dictionary document create script ( based on extended properties):

Create a SQL Server Data Dictionary in Seconds using Extended Properties

Hi Kalman,

Great content! This is very helpful.

How can I surface the data dictionaries in SharePoint? Also, can users modify object descriptions for a given database from within SharePoint and have that reflected in the database itself?

You can use Dataedo for generating data dictionary and export it to PDF, interactive HTML and Excel. It reads MS_Description and you can document directly inside repository. You can group all objects to modules (in repository, not DBMS) and paste imagest too!