SQL Server - Can you add field descriptions in CREATE TABLE?

Question

I can see plenty of posts about where the field description extended property lives and how I can get it, but nothing about adding these at the CREATE TABLE stage.

I'm dynamically creating tables so dynamically adding field descriptions would be a tidy thing to do but I cannot see a way.

Has anyone managed to do this?

score 38 · Accepted Answer · answered Dec 31 '09 at 13:37

While you can't do it in CREATE TABLE, you can do it at the same time, in the same database script, using this approach:

CREATE table T1 (id int , name char (20))

EXEC   sp_addextendedproperty 'MS_Description', 'Employee ID', 'user', dbo, 'table', 'T1', 'column', id

EXEC   sp_addextendedproperty 'MS_Description', 'Employee Name', 'user', dbo, 'table', 'T1', 'column', name

Then you can see your entries using this:

SELECT   *
FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'T1', 'column', default)

This works for me, but I had to tweak the `@level0Type` property to be `schema`, i.e.: `EXEC sp_addextendedproperty 'MS_Description', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;` — robyaw, Mar 30 '15 at 11:22

score 6 · Answer 2 · answered Apr 26 '18 at 05:32

SQL Server provides a system stored procedure that allows you to add descriptions, one name-value pair at a time

Example as follows:

EXEC sys.sp_addextendedproperty 
@name=N'Column 2 Description' -- Give your description a name
   , @value=N'blah blah 2 for a specific column' -- Actual description
   , @level0type=N'SCHEMA'
   , @level0name=N'dbo'
   , @level1type=N'TABLE'
   , @level1name=N'MyTestTable' -- Name of your table
GO

You would have to repeat for each description name-value pair

Hope this helps

score 4 · Answer 3 · answered Dec 31 '09 at 12:59

4

I don't believe the Create Table T-SQL statement supports this. However, if you are defining your tables via SSMS, you can easily enter table level and column level comments at the same time you create your table.

answered Dec 31 '09 at 12:59

Randy Minder

47,200
49
204
358

Hi, Thanks for the response, I know I'm clutching at straws here, I can do as you say via SSMS but I'm programmatically creating a table on the fly. I build up my CREATE TABLE command over a number of other routines and I was just wondering if I could jab in a usefull comment or two on the way. I'm kind of hoping that someone found a way or work around to do this. Thanks – Mike Dec 31 '09 at 13:06
1

If you are programmatically creating a table on the fly, why not also programmatically add the comment extended property as well? – Randy Minder Dec 31 '09 at 13:43

score 3 · Answer 4 · answered Mar 21 '18 at 00:58

In addition to the above, you can also use SSMS to do it. In SSMS, Right click on the table, select Properties, then click "Extended Properties" (on the left pane). On the right pane, in the middle, there is a "Properties" box, click in there to give your description a name, and some text, as shown in the attached picture

SQL Server - Can you add field descriptions in CREATE TABLE?

4 Answers4

Linked