Keeping documentation for a database in development current can be a pain and a waste of time because of the amount of changes. Redgate makes a great tool for this called SQL Doc, for $295 . Even though I love their products, I don’t need something that extensive or pricey for documentation . I’m only concerned with a few columns: table name, column name, nullable, default value, data type, length, and description. The key to using this is to make sure you keep the description fields up to date when you are creating columns. I like to put the below into a view on each database, then hit the view with a datagrid or in the report server to have an always up to date data dictionary.
[sql]
SELECT a.TABLE_NAME
, a.COLUMN_NAME
, a.IS_NULLABLE
, isnull(a.COLUMN_DEFAULT, ”) as COLUMN_DEFAULT
, a.DATA_TYPE
, isnull(a.CHARACTER_MAXIMUM_LENGTH, ”) as CHARACTER_MAXIMUM_LENGTH
, isnull((SELECT x.value FROM sys.extended_properties AS x
LEFT OUTER JOIN sys.columns AS C ON C.object_id = x.major_id
AND C.column_id = x.minor_id
where OBJECT_NAME(x.major_id) = a.TABLE_NAME
and c.name = a.COLUMN_NAME
), ”) as ColumnDescription
FROM INFORMATION_SCHEMA.COLUMNS as a
WHERE a.TABLE_NAME <> ‘sysdiagrams’
ORDER BY a.TABLE_NAME, a.COLUMN_NAME
[/sql]