4 ways to edit column descriptions in SQL Server Management Studio

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

This one is a little hack I found on this blog. You use diagram editor built into SSMS to get most convenient description editor. Setting diagrams up is a little pain, though. See a quick guide.

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

This one is a hack to SSMS shared by pithhelmet on StackOverflow which requires you to update the values in system registry.

NOTE: Please be careful when editing the registry.

  1. Hit Windows Start and type 'regedit'.
  2. Registry Editor should open.
  3. Find HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\12.0\DataProject element
  4. 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.
  5. 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.
  6. Type in 1,2,6,17; and confirm with OK button.
  7. Restart SSMS.

Voila! You can now edit columns in inline editor.

Available fields:

  1. Column Name
  2. Data Type
  3. Length
  4. Precision
  5. Scale
  6. Allow Nulls
  7. Default Value
  8. Identity
  9. Identity Seed
  10. Identity Increment
  11. Row GUID
  12. Nullable
  13. Condensed Type
  14. Not for Replication
  15. Formula
  16. Collation
  17. Description

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:

See live sample

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.

Try it for free now

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.