SQL Server Management Studio is a decent tool. However, it has many shortcomings and guys at MS don't bother to do anything about it for, well, over a decade now. It's a bit annoying considering that SQL Server database is very popular and costs quite a lot of $.
One of its weak features is the functionality to describe tables and columns. Interestingly, Microsoft is a leader (in my opinion) in RDBMS market in terms of metadata capabilities with their proprietary extended properties. In SQL Server you can add multiple custom properties to each database schema element. Compare that with capabilities of Oracle database (Oracle is 4 times more expensive).
But MS didn't bother to provide useful user interface for those metadata fields and I believe documenting database schema is second most important thing, right after design itself. Developers and DBAs skip that step as providing descriptions is simply too painful.
In this article I will show you all the options (I know) to edit descriptions with SSMS. Some of you might surprise you and may be the cure for the pain that using standard options was.
Let's first have a look at 4 different ways you can edit table column descriptions.
The basic one. Open table designer. Select table using a right-click and choose "Design". Then select a column you want to describe and go to Description field in the properties editor on the bottom of the screen. It's a lot of mousework, isn't it?
You can extend the editor by clicking [...] button on the right side of field.
Another option is to go to column Extended Properties editor. This one you would only use when you want to add properties other than 'MS_Description'. In the navigation click Columns element and select column. Right click column and choose Properties.
In the properties dialog select Extended Properties tab - it lists all extended properties assigned to the your column. To edit the description find property named 'MS_Description'. If there's isn't one you need to create it.
Create new diagram, add to it tables you want to edit and change view of table to Custom - right click table, select Table View and choose Custom.
Open edit dialog for Custom view and then add a Description field to your view.
And enjoy the ability to edit descriptions in a table.
NOTE: Please be careful when editing the registry.
Voila! You can now edit columns in inline editor.
Now, let's describe tables. We have two options.
The case is similar to the one with columns but less obvious. Do you remember, we can describe tables from the designer? You need to enable table properties editor first. Right-click on a white space and select Properties option from the menu. Then you can edit table description in a Description field.
Another option, also the same, as above, is to use extended properties editor. To enter the editor right-click on a table and select Properties option. Then go to Extended Properties tab and find 'MS_Description' property. If there isn't one, simply create it.
Once you have descriptions in your database it's time to export them to an easily accessible HTML format so you can use and share the documentation. One of the best tools for this purpose is Dataedo. In few clicks you can generate documentation like the one below:
It is also very convenient metadata editor. It offers descriptions and custom fields edition, not just for tables but also many of the database elements (including stored procedures, views, and triggers). You can create ER diagrams to visualize data model, group objects into modules and document missing FK constraints.