Adding a column description

Question

Does anyone know how to add a description to a SQL Server column by running a script? I know that you can add a description when creating a column using SQL Server Management Studio.

How can I do this script when my SQL scripts create a column, also a description for the column is added?

Answer 1

I would say that you probably want to do this using the sp_addextendedproperty stored proc.

Microsoft has some good documentation, but you can also look at this link:

http://www.eggheadcafe.com/software/aspnet/32895758/how-to-set-description-property-with-alter-table-add-column.aspx

Try the following:

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Hey, here is my description!', @level0type = N'Schema', @level0name = 'yourschema', @level1type = N'Table', @level1name = 'YourTable', @level2type = N'Column', @level2name = 'yourColumn'; GO 

Answer 2

This works for me. The corresponding arguments are indicated by small arrows.

 EXEC sys.sp_addextendedproperty @name=N'MS_Description' ,@value=N'Here is my description!' --<<<< ,@level0type=N'SCHEMA' ,@level0name=N'dbo' ,@level1type=N'TABLE' ,@level1name=N'TABLE_NAME' --<<<< ,@level2type=N'COLUMN' ,@level2name=N'FIELD_NAME' --<<<< 

Answer 3

  EXEC sys.sp_addextendedproperty @name = N'MS_Description ', @value = N'extended description ', @level0type = N'SCHEMA ', @level0name = N'dbo ', @level1type = N'TABLE ', @level1name = N'Table_1 ', @level2type = N'COLUMN ', @level2name = N'asdf ' > 

Create a script on the table [dbo]. [Table 1]

Answer 4

In MS SQL Server Management Studio 10.0.55 the easiest way:

• Display columns for table in object browser window
• Right-click the column of interest and click Modify.
• Look in the "Column Properties" window (in the lower right corner of my GUI) \
• Take a look in the “Table Designer” section.
• Change the value for the Description line
• Click "x" in the upper right corner of the column change window / tab
• Answer “y” when he says that the changes apply.

If you right-click on your table in the "Object Explorer" window and select properties, then click "Advanced Properties", you should see your comment.

Note that if you use the Script Table As command for the table, then the above Description column is still not displayed as a comment for the column. Instead, it displays an additional call to sp_addextendedproperty after creating the table. Satisfactory.