Where is column description located in a system table

Question

Where is column description located in a system table

Fred Wahlquist

SSC Eights!

Points: 874
More actions
May 5, 2008 at 4:48 pm

#190286

We've been connecting to our Sql Server database via Visual Studio and opening the table definition and documenting the column description in the Column Properties tab. I expect the description is stored in a system table somewhere but I haven't found an information schema view or stored procedure to get a the column descriptions. My goal is just to query the column names and descriptions to add to our documentation. Is there a system view that shows me this? Thanks - Fred

Viewing 15 posts - 1 through 15 (of 22 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

I believe that it is an Extended Property.

[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]

Anam Verma SSCertifiable Points: 5365 More actions · Answer 2

i think this is what you want

select b.*

from sys.objects a inner join sys.columns b on a.object_id = b.object_id

where a.[name] = 'test1'

replace 'test1' with the table name

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

helloanam (5/6/2008)
i think this is what you want
select b.*
from sys.objects a inner join sys.columns b on a.object_id = b.object_id
where a.[name] = 'test1'
replace 'test1' with the table name

No, that returns the column definition values, it does not have the column_description in it. you have to go to sys.extended_properties for that.

[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]

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

hi

what about using fn_listextendedproperty check out BOL for more details.

"Keep Trying"

Anam Verma SSCertifiable Points: 5365 More actions · Answer 5

Anam Verma

SSCertifiable

Points: 5365

May 6, 2008 at 3:08 am

#811065

sorry RBarry, i mixed both. My apologies

Fred Wahlquist SSC Eights! Points: 874 More actions · Answer 6

Thanks to all that pointed me in the right direction. For others that need this I found in BOL this example that uses the sys.extended_properties view, simpler than using the extended properties function.

SELECT major_id, minor_id, t.name AS [Table Name], c.name AS [Column Name], value AS [Extended Property]

FROM sys.extended_properties AS ep

INNER JOIN sys.tables AS t ON ep.major_id = t.object_id

INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id

WHERE class = 1;

Thanks - Fred

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

That SQL was helpful and gave me what i was looking for. Thanks to those who put it here. Another question:

I have column comments as Extended Properties in one database which is outdated. Another database is in use and does not have column comments. Is there a way to get the extended properties from database1 to database2.

Can system tables be updated?

Is it safe to update system tables?

GSquared SSC Guru Points: 260824 More actions · Answer 8

I don't believe you can update those directly in the system tables. Haven't tried, but I'll be shocked if you can do it.

What you might be able to do is get a create script for all of the descriptions in the obsolete database, and run that in the new database. TEST FIRST in a database you can drop if you blow it up.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 9

Now... that brings up a very interesting question... I looked at sp_AddExtendedProperty and there's nothing in it that would enable the ability to modify system tables directly... even if you drill down through some of the sprocs it uses... but IT does!

How does it do that?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Jeff:

My understanding is that only objects actually IN the ResourcesDB can directly access other ResourcesDB objects. And all of the 'sys.*' objects are actually in the ResourcesDB, but are projected into the SYS schema as a way to indirectly access them.

[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]

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 11

Yep... I understand that... but why does it work in SQL Server 2000? What is it that allows sp_AddExtendedProperty to add a row to sysProperties without, apparently, having to allow changes to systems tables? Do you suppose it's something in the server .exe that says "Oh, yeah... if they're using that sp, it's ok"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

Hmm, good question.

[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]

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

Jeff Moden (6/4/2008)
Yep... I understand that... but why does it work in SQL Server 2000? What is it that allows sp_AddExtendedProperty to add a row to sysProperties without, apparently, having to allow changes to systems tables? Do you suppose it's something in the server .exe that says "Oh, yeah... if they're using that sp, it's ok"?

Is it considered an 'ad-hoc update' when the update is originating through a SYSTEM stored proc?

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

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 14

Hmmmm.... that food for thought. I'm gonna have to make an SP in master that does an update on sysProperties and label it as a system proc... it will be really interesting if that works...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)