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?

Phrygia

Well, it's a legacy DB and the column names were descriptive to the person who wrote the columns 5-6 years ago, but they don't make a lot of sense to me. There are lots of business rules that go along with the columns, so it would be nice to just have a handy description of the why the column is there. I figure we comment our code why shouldn't we comment the DataBase? – Phrygia

Here's a pure sql script I wrote that does bi-directional ms_description management: gist.github.com/timabell/6fbd85431925b5724d2f - feel free to steal relevant bits and use them, or just use the whole thing. I'm on a mission to improve all the databases! – Ant

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.

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

Curbstone

I'm kind of an idiot when it comes to SQL, I just learn whatever I need whenever I need it and no more, so forgive me when I ask this ridiculous question. How do I figure out what "yourschema" is? – Phrygia

@EJC, it is most likely "dbo" – Erdmann

it's probably 'dbo', that's the default when you create a table. usually your table names will be displayed like this: "{something}.tableName". the {soemthing} is the schema. – Curbstone

Property 'MS_Description' does not exist for 'dbo.migrationHistory.MigrationStatus'. – Jannelle

Answer 2

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'  --<<<<

Shanonshanta

This answer uses the proper 'name' value (MS_Description) to have the description appear in the table designer window. This isn't entirely necessary, but it is convenient and a nice way to draw attention to the presence of extended properties on a column. – Malinin

Property 'MS_Description' does not exist for 'dbo.migrationHistory.MigrationStatus' – Jannelle

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 script on table [dbo].[Table_1]

Literatim

Property 'MS_Description' does not exist for 'dbo.migrationHistory.MigrationStatus' – Jannelle

Answer 4

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.

Milkweed

Upvoted for mentioning that once you've added one then ssms can show you the script you need – Ant

this is the best description I've seen even into 2024 because not only does it work to show you how to do it in 2005, but also in new versions. simply apply the change in designer and then use script to create and it shows you the current version to create the change. blamo! – Jannelle

Answer 5

SSMS 2016 (and explains ALL versions of SQL)

EXEC sys.sp_addextendedproperty 
    @name=N'MS_Description', @value=N'this is description yooo',
    @level0type=N'SCHEMA',@level0name=N'dbo', 
    @level1type=N'TABLE',@level1name=N'yourtablenamehereee',
    @level2type=N'COLUMN',@level2name=N'yourcolumnnamehereee'

Discovered with Ben Slade's answer which will help anyone solve the problem for any version of SSMS or any SQL editor for that matter.

• Simply go into the designer/editor and make the changes to a example/sample/target table
• then use the script as to create option...
• it will output the correct target syntax for your version of sql

My issue was that I was missing the <sys.> at the beginning of the sp_addextendedproperty. Generating the solution showed me the answer which I imagine would solve many problems for many people.

Jannelle