adding a column description
SqlSql ServerSql Server-2005Sql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
I'd say you will probably want to do it using the sp_addextendedproperty stored proc.
Microsoft has some good documentation on it.
Try this:
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
Solution 2 - Sql
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' --<<<<
Solution 3 - Sql
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 script on table [dbo].[Table_1]
Solution 4 - Sql
In MS SQL Server Management Studio 10.0.55, the easiest way is to:
- Display the columns for the table in the Object Explorer window
- Right click on the column of interest and click on the "Modify" option
- Look in the "Column Properties" window (in the lower right in my GUI)\
- Look in the "Table Designer" sub section
- Modify the value for the "Description" row
- Click on the "x" in the upper right of the column modification window/tab
- Answer "y" when it says apply changes
If you then right click on your table in the Object Explorer window and click on properties, then click on "Extended Properties", you should see your comment.
Note, if you do a "Script Table As" command for the table, the above column "Description" still doesn't show up as a comment for the column. Instead it shows an extra sp_addextendedproperty call after the table create. Mediocre.