Table of Contents:


    Follow us

    How to edit table descriptions in SSMS (4 options)

    Adam Adamowicz - Dataedo Team Adam Adamowicz 2018-06-25 2019-02-20
    This applies to SQL Server Management Studio (SSMS). Article available also for: MySQL Workbench Oracle SQL Developer pgAdmin phpMyAdmin Dbeaver SQL Workbench/J

    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.

    Column Descriptions

    Let's first 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

    Table Descriptions

    Now, let's describe tables. We have two options.

    Option 1: Table Properties

    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.

    Option 2: Properties Editor

    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.

    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

    Share this article

    Comments (0)