In the continuing battle over database documentation, Jon Galloway has released a new .NET 2.0 weapon (with source!):
Data Dictionary Creator
Data Dictionary Creator collects extended properties/descriptions for SQL Server tables and columns and outputs the resulting documentation to a variety of formats. It's also easy to use, and did I mention the source is downloadable too?
A tool I've used in the past for retroactively adding extended properties to views, stored procedures, and stored procedure parameters is the no-longer available XpressProps. Nowadays, I add extended properties to both the procedure and all parameters while scripting the object creation - here's some script to do this in SQL Server 2000:
EXEC sp_addextendedproperty N'MS_Description', N'*** Procedure header goes here ***
', N'user', N'dbo', N'procedure', N'*** Procedure name goes here ***'
EXEC sp_addextendedproperty N'MS_Description', N'*** Parameter description goes here ***
', N'user', N'dbo', N'procedure', N'*** Procedure name goes here ***', N'parameter', N'*** Parameter name (including @) goes here ***'
Use this script at your own discretion. I make no guarantees or warranties.
On a side note: as I've already blogged, although I script the creation of views and stored procedures (and as I've described, you can add extended properties/descriptions in the creation script), I still design tables in Enterprise Manager, so Jon's tip of adding descriptions in the SQL Server Diagram tool is pretty helpful. Thanks mate!
Tags: sql server, documentation, database
posted @ Friday, October 06, 2006 1:08 AM