SQL Server has this neat feature other database engines are lacking - extended properties. Those is are the custom metadata fields you can attach to almost any database schema element - table, column, key etc. You can use it to document database structure. In this article I'm considering pros and cons of this approach and I'm presenting an alternative.

Pros

1. Metadata always with data

Key benefit of storing metadata in extended properties (or comments in other databases) is that it is always with the data. It's easy to find and maintain it.

2. Other tools can access metadata

Some tools, like BI or data profiling tools, can access metadata from extended properties and present it to the user making reporting and data discovery an easier task.

Cons

1. Hard to browse and edit

Reading extended properties with SQL Servers default tool, SSMS, is very cumbersome. To view column comments you need to enter design mode and select a specific column.

2. You need write access to the database

To be able to read or edit metadata you need to have access to the database itself, which is not always possible or desirable. In some environments access to databases is strictly limited to DBAs and selected developers. But it would be desired for other team members to describe data, which might not be possible or be difficult.

3. Length and format limitations

You are limited to plain text fields and up to 7,500 characters.

Alternative

I'd like to present you an alternative, or rather a mixed approach, taking best of both of the worlds - metadata within database and metadata in a separate metadata repository using Dataedo. Dataedo is a database documentation tool with its own repository and you can easily scan metadata from multiple databaes, describe schema elements (including rich text with images), and write back to the extended properties. But what is most amazing is the ability to share your schema and descriptoins in useful HTML.

See live sample