adding a column description

Question

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

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

Answer 1

I'd say you will probably want to do it using the sp_addextendedproperty stored proc.

Microsoft has some good documentation on it 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 this:

EXEC sp_addextendedproperty 
@name = N'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

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

create script on table dbo.Table_1

Answer 3

This works for me. Relevant arguments are indicated with little 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'  --<<<<