How to include column Description in 'Create Table' script

SQL Server 2005
I have populated the Description field for several columns in a table, and would like to keep them when I create a script to create the table.  How can this be done?
Who is Participating?
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
from BOL

B. Adding an extended property to a column in a table
The following example adds a caption property to column PostalCode in table Address.

 Copy Code
USE AdventureWorks;
EXEC sp_addextendedproperty
@name = N'Caption', @value = 'Postal code is a required column.',
@level0type = N'Schema', @level0name = Person,
@level1type = N'Table',  @level1name = Address,
@level2type = N'Column', @level2name = PostalCode;
jkornitAuthor Commented:
Excellent.  I accidentally found it just before reading your post (after I searched for about 20 minutes in SSMS Options and Help).  By going to Options | Scripting | Script Extended Properties, I finally got SSMS to script the description properties just as you showed.
Kevin CrossChief Technology OfficerCommented:
As Aneesh has shown.  If you put the description or caption in as an extended property of the column(s) in your table.  When you open SQL Management Studio and right-click on the table name and select:

Script Table as --> CREATE To --> New Query Editor Window

You will see that the generated SQL includes to the table creation as well as addition of extended property using the stored procedure shown.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.