Indeed, Extended Properties (MS_Description) is the way to go. Having these descriptions readily available as a part of the metadata could be utilized not only by docs generators but also (hopefully one day) by tools that provide "intellisense" for example the excellent Softtree's SQL Assistant http://www.softtreetech.com/isql.htm (last time I checked they didn't) or built in SQL Sever Management Studio's Intellisense (since sql2008)

I also believe it should be easy for devs and DBA's to add these notes because as Tangurena and Nick Chammas correctly pointed out - devs are very reluctant to keep the docs updated and hate duplicate work - which is fair enough especially for a person who was taught to optimize things during their entire professional life. So unless it's really easy to update docs in one place close to source code - this isn't gonna work. At some point I searched the web and didn't find a solution to this so I wrote LiveDoco (not free, sorry) in attempt to make it easy. More info here if interested: http://www.livedoco.com/why-livedoco

Indeed, Extended Properties (MS_Description) is the way to go. Having these descriptions readily available as a part of the metadata could be utilized not only by docs generators but also (hopefully one day) by tools that provide "intellisense" for example the excellent Softtree's SQL Assistant http://www.softtreetech.com/isql.htm (last time I checked they didn't) or built in SQL Sever Management Studio's Intellisense (since sql2008)

I also believe it should be easy for devs and DBA's to add these notes because as Tangurena and Nick Chammas correctly pointed out - devs are very reluctant to keep the docs updated and hate duplicate work - which is fair enough especially for a person who was taught to optimize things during their entire professional life. So unless it's really easy to update docs in one place close to source code - this isn't gonna work. 

At some point I searched the web and didn't find a solution to this so I wrote LiveDoco (not free, sorry) in attempt to make it easy. More info here if interested: http://www.livedoco.com/why-livedoco (Website / LiveDoco is dead as of 5/4/2020).