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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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
MehramAuthor Commented:
Thanks and regards
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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

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
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.
MehramAuthor Commented:
Ok.
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.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Thanks for sharing that..
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.