Where is column description located in a system table

Question

Where is column description located in a system table

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

I'd make sure to test that on a 2000 box. the sp_helpText returns something entirely different for sp_AddExtended in 2005. There's no mention of sysProperties.

Rut roh....looks a bit like CLR/ADO...:P

EXEC %%ExtendedPropertySet().AddValue(Name = @name,

Value = @value,

Level0type = @level0type,

Level0name = @level0name,

Level1type = @level1type,

Level1name = @level1name,

Level2type = @level2type,

Level2name = @level2name)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

noeld SSC Guru Points: 96590 More actions · Answer 2

Matt Miller (6/4/2008)
I'd make sure to test that on a 2000 box. the sp_helpText returns something entirely different for sp_AddExtended in 2005. There's no mention of sysProperties.
Rut roh....looks a bit like CLR/ADO...:P
EXEC %%ExtendedPropertySet().AddValue(Name = @name,
Value = @value,
Level0type = @level0type,
Level0name = @level0name,
Level1type = @level1type,
Level1name = @level1name,
Level2type = @level2type,
Level2name = @level2name)

%% Symbol is used for internal objects and yes CLR is definitely involved 😉

* Noel

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

noeld (6/4/2008)
Matt Miller (6/4/2008)
I'd make sure to test that on a 2000 box. the sp_helpText returns something entirely different for sp_AddExtended in 2005. There's no mention of sysProperties.
Rut roh....looks a bit like CLR/ADO...:P
EXEC %%ExtendedPropertySet().AddValue(Name = @name,
Value = @value,
Level0type = @level0type,
Level0name = @level0name,
Level1type = @level1type,
Level1name = @level1name,
Level2type = @level2type,
Level2name = @level2name)
%% Symbol is used for internal objects and yes CLR is definitely involved 😉

Thanks - that's how I was reading it. Just wanted to make sure whatever test Jeff wanted to take on didn't just fall down thanks to running it against the wrong DBEngine.

It figures they would use CLR to obfuscate interacting with the dmv's...Of course - that's the "real" CLR....:Whistling:

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

shailendra-251422 Ten Centuries Points: 1111 More actions · Answer 4

I tried the procedure sp_addextendedproperty and it does what I need. Thanks Jeff, Matt, RBarry, Noel, and Gsquared.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Glad we could help.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 6

I noticed the other day (which may or may not be relevant) that (things like column descriptions) added in the the table designer are added as an extended property with the name MS_Description. (I add an extended property named "Description" to most of my objects, and was pondering (but didn't try) loading a bunch of column descriptions from my data dictionary, perhaps as "MS_Description" rather than "Description" so they would show up for others in the table designer.)

I have also noticed (and am unable to understand) that when you create a new extended property from within the Management Studio, it is very slow to process -- yet doing it via TSQL (even capturing the sql generated by management studio) runs fast. But the extended property values all wind up in sys.extended_properties anyway.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Not sure I added anything to your initial question - but thanks for the feedback!

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

patelekta SSC Rookie Points: 48 More actions · Answer 8

Did you get any solution to this question. My requirement is bit similar to this, I need to update all column description where 'Information' is spelled like 'Informaiton'. I am using following query, which is not working,

update sys.extended_properties

SET value = replace(cast( value as varchar(500)), 'Informaiton', 'Information')

WHERE class = 1

Thanks...