Setting SQL Server Field Descriptions

This is Part 2 in a series of articles discussing field comments on linked tables.

Linked SQL Server Tables

Like linked MS Access tables, you can add (or overwrite) column descriptions in the front-end database.  Of course, the same limitation applies.  If you re-link the table, the descriptions you modified in the front-end are lost.

As with tables linked to back-end MS Access files, the best approach is to enter your field descriptions in the source tables themselves.  

Doing this in a SQL Server back-end is both easier and harder than in an Access back-end.  It's easier in SQL Server because the changes can be made without having to get all the users out of the affected table(s).  But it's harder because the descriptions do not automatically get imported into Access as part of the table linking process.  (Stay tuned...I'll show you how to do that in a later article in this series.)

Setting the description via the GUI

In SQL Server Management Studio (SSMS), you can right-click on an existing table and choose "Design."  Then, in the table designer, place your cursor in the row of the column whose description you want to set.  The "Column Properties" tab will appear below the table designer.  Simply set the "Description" value to whatever you want.  Be sure to hit the save button to apply the changes to the table:

Setting the description via T-SQL

To set the field description in T-SQL, you need to use an SQL Server extended property named "MS_Description."  This extended property is similar to Microsoft Access database properties in that you will need to create the property if it does not already exist.

T-SQL to Add a New Field Description

The following T-SQL will add a field description to the PhoneNum field in the Contact table within the default dbo schema using the built-in stored procedure sp_AddExtendedProperty:

exec sp_AddExtendedProperty 
      'MS_Description'
    , 'The phone number (e.g., "(555) 253-5970 x1150"'
    , 'SCHEMA', 'dbo'
    , 'TABLE', 'Contact'
    , 'COLUMN', 'PhoneNum'

T-SQL to Update an Existing Field Description

The above T-SQL will fail if the MS_Description extended property already exists on the table.  In that case, we would need to use the sp_UpdateExtendedProperty stored procedure instead.  For example:

exec sp_UpdateExtendedProperty 
      'MS_Description'
    , 'The phone number (e.g., "555-253-5970"'
    , 'SCHEMA', 'dbo'
    , 'TABLE', 'Contact'
    , 'COLUMN', 'PhoneNum'

Required permissions

Please note that you will need one of the following permissions to set field Description values in SQL Server:

Be a member of one of these database roles:

• db_owner
• db_ddladmin

Or, be granted one of the following table-level permissions:

• ALTER
• CONTROL

Image by Pexels from Pixabay