26

Possible Duplicate:
SQL Comments on Create Table on SQL Server 2008

I just want to know how to add a comment to an existing table column in SQL Server? Seems simple, but I just don't find anything in the 5 first results that throw my search engine.

edits

Rather than using the UI, I would to know the SQL query.

Community
  • 1
  • 1
Rubens Mariuzzo
  • 28,358
  • 27
  • 121
  • 148

2 Answers2

36

While creating a new table in SQL Server Management Studio, see this screenshot for adding a description to a column:

enter image description here

To do it programmatically:

EXEC sp_updateextendedproperty 
@name = N'MS_Description', @value = 'Your description',
@level0type = N'Schema', @level0name = dbo, 
@level1type = N'Table',  @level1name = Your Table Name, 
@level2type = N'Column', @level2name = Yuur Column Name;
Pang
  • 9,564
  • 146
  • 81
  • 122
Pankaj
  • 9,749
  • 32
  • 139
  • 283
  • 4
    Rather than using the UI, I would to know the SQL Query. – Rubens Mariuzzo Jan 26 '12 at 13:32
  • 3
    A nice little trick is to define your description in the GUI using the designer, save it, then fetch the create script using the context menu. This way you get the whole description code auto generated. – André C. Andersen Feb 26 '15 at 17:37
  • Programmatic example updates an existing extended property. It will error if the property doesn't yet exist. You need `sp_addextendedproperty` to add a new property. – Travis Jun 01 '23 at 16:05
21

That depends on what you mean by "comment". If you want to add descriptive text to a column, you can set the Column Description using SQL Server Management Studio:

To set the description programmatically, you can use the sp_addextendedproperty, sp_updateextendedproperty and sp_dropextendedproperty stored procedures. Example:

EXEC sp_addextendedproperty 
    @name = N'MS_Description', @value = 'This is the description of my column',
    @level0type = N'Schema', @level0name = 'dbo',
    @level1type = N'Table', @level1name = 'MyTable', 
    @level2type = N'Column', @level2name = 'MyColumn'

I admit that the syntax is a bit inconvenient -- the following blog post contains stored procedures that make this process a bit easier:

Heinzi
  • 167,459
  • 57
  • 363
  • 519
  • 2
    which is "extended properties" using SQL http://msdn.microsoft.com/en-us/library/ms190243.aspx – gbn Jan 26 '12 at 13:15
  • +1 I completely misread the question. – Lieven Keersmaekers Jan 26 '12 at 13:20
  • 2
    Rather than using the UI, I would to know the SQL Query. – Rubens Mariuzzo Jan 26 '12 at 13:31
  • 1
    @Heinzi => sp_addextendedproperty Adds a new extended property to a database object.http://msdn.microsoft.com/en-us/library/ms180047.aspx Please check below answer. Because the property is already there in the list....I will not downvote this post so please update your answer... – Pankaj Jan 26 '12 at 13:56
  • I think it should be `sp_updateextendedproperty` instead of `sp_addextendedproperty`. – Rubens Mariuzzo Jan 26 '12 at 14:03
  • 1
    @StackOverflowUser: I beg to differ: The MS_Description property does *not* exist until it has explicitly been created (either through sp_add... or by setting it in SSMS). You are right, though, that sp_update- and sp_dropextendedproperty should be mentioned as well. Thanks and +1! – Heinzi Jan 26 '12 at 14:06