Joey D’Antoni gives us a primer on database documentation techniques:
Although writing better queries and building the right indexes are important parts of improving database performance, building clear database documentation can also contribute to this goal by helping you understand your database architecture. Painting a clear picture of the structure of your database gives you insight into your data flows and helps you identify redundant data and clarify business processes.
Let’s take a look at a few approaches you can take to documenting your database and your data, depending on the nature of your application.
I do like the idea of using extended properties for documentation purposes, though for the longest time, the tooling to show those extended properties was fairly limited and it was easy to miss them on scripting.