Column description - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Column description

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-16 : 09:26:00

Partha writes "I have entered a description for every column in my table in design mode. However I am not able to see that description in any system table? Where is it stored? How can I retrieve it in a SQL statement?"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-16 : 10:30:37

quote:
I have entered a description for every column in my table in design mode.

Really? What version of SQL Server? I've never seen that functionality.

<O>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-16 : 10:56:22

It's in Enterprise Manager, when you create a table, there is a text box to store a description for that column.

Column descriptions are stored in the sysproperties table, but they're stored as binary data. I was able to make this query work though:

SELECT so.Name, sp.value
FROM sysobjects so INNER JOIN sysproperties sp
ON so.id=sp.id
WHERE so.name='myTable' AND sp.name='MS Description'

I think that as long as the data being returned is textual then it should display correctly.

mcp111
Starting Member

44 Posts

Posted - 2002-07-16 : 12:41:46

That's cool!!
Incidentally it should be MS_Description.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-16 : 15:19:43

You can also use fn_listextendedproperty.
For example to list all the extended properties of the Customers table in the Northwind database we use

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

and in your case, as you point out, the type of extended property created by adding descriptions using Enterprise Manager has the name MS_Description so we can add a where clause

SELECT *
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'Customers', 'column', default)
where name='MS_Description'

to filter out any user extended properties you might have added external to Enterprise Manager.

HTH
Jasper Smith

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-16 : 16:20:50

Is this a SQL2K thing only? or am I losing my mind?

<O>

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-16 : 16:39:39

Yes its new in SQL 2000 along with the associated procedures

sp_addextendedproperty

sp_dropextendedproperty

sp_updateextendedproperty

HTH
Jasper Smith

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-16 : 16:42:56

And you are losing your mind too...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources