23

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
  • 27,091
  • 26
  • 115
  • 147

2 Answers2

35

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,073
  • 146
  • 84
  • 117
Pankaj
  • 9,342
  • 26
  • 119
  • 251
16

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
  • 159,022
  • 53
  • 345
  • 499
  • 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
  • @RubensMariuzzo: I updated my answer. – Heinzi Jan 26 '12 at 13:41
  • 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