Alter description property of a colunm of a table.

In SQL Server 2005, it is possible to add a text description of a table column by using SSMS.

http://www.winnershtriangle.com/w/Articles.DocumentingSQLServer2005Databases.asp

Can I do the same using sql code.

I want syntax for both add / alter.

MehramAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes, you can add description property to a table column using sp_addextendedproperty stored procedure like this

USE AdventureWorks;
GO
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;
GO

More info here:

http://msdn.microsoft.com/en-us/library/ms180047.aspx

Note: If you try to alter description using sp_addextendedproperty, then run this procedure again which would overwrite the existing values.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> I want syntax for both add / alter.

So to Add, run the code first time
To Alter, re-run it again with the modified description values.

And to view the Descriptions added to the column of a table, you can use this script:

USE AdventureWorks;
GO
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'Production', 'table', 'ScrapReason', 'column', default);
GO

More info here:
http://msdn.microsoft.com/en-us/library/ms179853.aspx
0
MehramAuthor Commented:
Thanks and regards
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MehramAuthor Commented:
Hi rrjegan17

I want to add description "That the quick brown fox' to column class of table dbo.class

I did this

EXEC sp_addextendedproperty
@name = N'Caption', @value = 'That the quick brown fox',
@level0type = N'Schema', @level0name = dbo,
@level1type = N'Table',  @level1name = trpt_arg_dtl,
@level2type = N'Column', @level2name = class;


The code executed without error, but the description has not been added, what is the error.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
This works fine at my end:
check out whether you miss anything at your end.
use ur_db_name
go

create table test ( a int, b int)

EXEC sp_addextendedproperty
@name = N'Caption', @value = 'That the quick brown fox',
@level0type = N'Schema', @level0name = dbo,
@level1type = N'Table',  @level1name = test,
@level2type = N'Column', @level2name = a;

SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'test', 'column', default);

Open in new window

0
MehramAuthor Commented:
Hi

SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'test', 'column', default);
result is
objtype      objname      name      value
COLUMN      a      Caption      That the quick brown fox

But when I look it throug ssms I dont see any value in description property

bmp attached
description-is-missing.bmp
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Got it..
SSMS would not list out any descriptions added via sp_addextendedproperty
And you need to use fn_listextendedproperty to view the description and this kind of functionality change was brough in SQL Server 2005 and 2008. In SQL Server 2000, you would be able to view it.
0
MehramAuthor Commented:
Ok.
0
MehramAuthor Commented:
Hi

I added a description through SSMS

ran your code
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'test', 'column', default);

came to know that the name of the propery we are discussing is "MSDescription"

so I tried

EXEC sp_addextendedproperty
@name = N'Ms_Description', @value = 'That the quick brown fox',
@level0type = N'Schema', @level0name = dbo,
@level1type = N'Table',  @level1name = test,
@level2type = N'Column', @level2name = a;

It worked.
I can see it through SSMS also.

Thanks and very best regards.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Thanks for sharing that..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.