63

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?

p.campbell
  • 98,673
  • 67
  • 256
  • 322
EJC
  • 2,062
  • 5
  • 21
  • 33
  • 8
    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? – EJC Sep 20 '10 at 18:18
  • Here's a pure sql script I wrote that does bi-directional ms_description management: https://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! – Tim Abell Jun 19 '19 at 07:26

4 Answers4

73

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
Madis Otenurm
  • 61
  • 1
  • 7
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • 1
    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? – EJC Sep 20 '10 at 18:22
  • 2
    @EJC, it is most likely "dbo" – KM. Sep 20 '10 at 18:25
  • 2
    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. – Abe Miessler Sep 20 '10 at 18:25
28

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'  --<<<<
JosephStyons
  • 57,317
  • 63
  • 160
  • 234
  • 1
    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. – AperioOculus May 01 '15 at 14:18
10
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]

DDuffy
  • 413
  • 1
  • 4
  • 21
DForck42
  • 19,789
  • 13
  • 59
  • 84
5

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.

Ben Slade
  • 478
  • 5
  • 11