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 have a look at 4 different ways you can edit table column descriptions:
Option 1: Table designer
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.
Option 2: Properties editor
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.
Option 3: Diagram
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.
Option 4: Description property in the designer
NOTE: Please be careful when editing the registry.
- Hit Windows Start and type 'regedit'.
- Registry Editor should open.
- Find HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\12.0\DataProject element
- Find SSVPropViewColumnsSQL70 and SSVPropViewColumnsSQL80 keys. Default values are 1,2,6; and they define columns visible in the editor. Please see a list of fields in the list below.
- To add the description (17) field to the end of the list of properties change the value to 1,2,6,17;. Right-click on the key and select Modify option.
- Type in 1,2,6,17; and confirm with OK button.
- Restart SSMS.
Voila! You can now edit columns in inline editor.
- Column Name
- Data Type
- Allow Nulls
- Default Value
- Identity Seed
- Identity Increment
- Row GUID
- Condensed Type
- Not for Replication
Bonus: Export to HTML
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.