If you use the GUI to modify a table in Management Studio Express,
there's a column property called "Description."

I'd like to be able to set and access that property using SQL.

I played with sp_addextendedproperty and fn_listextendedproperty but
this particular "Description" doesn't seem to be an extended property.
Unless it has some cryptic name other "Description" or "Column
Description".

Am I missing something or Is this just something that's not
read/writeable with SQL?

TIA,

-Dan

Sadly, it's called MS_Description, not a commonly known fact from my
experience.  The good news is, you should be able to get at it without using
the extended property functions.


SELECT
    [Table Name] = OBJECT_NAME(c.object_id),
    [Column Name] = c.name,
    [Description] = ex.value
FROM
    sys.columns c
LEFT OUTER JOIN
    sys.extended_properties ex
ON
    ex.major_id = c.object_id
    AND ex.minor_id = c.column_id
    AND ex.name = 'MS_Description'
WHERE
    OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0
    -- AND OBJECT_NAME(c.object_id) = 'your_table'
ORDER
    BY OBJECT_NAME(c.object_id), c.column_id;


(And I hesitate to suggest it, but this is not the best way to document your
database, if that is the intention.)




Show quoteHide quote

Aaron Bertrand wrote...

Yeah...I'm beginning to get that feeling, but I appreciate knowing
about "MS_Description" just in case I need to work with a database
authored by someone who felt differently :)

So, what would you recommend as an alternative? I'm thinking of going
low-tech and just putting some comments in my DDL script.

Thanks,

-Dan

Sadly, it's called MS_Description, not a commonly known fact from my
experience.  The good news is, you should be able to get at it without using
the extended property functions.


SELECT
    [Table Name] = OBJECT_NAME(c.object_id),
    [Column Name] = c.name,
    [Description] = ex.value
FROM
    sys.columns c
LEFT OUTER JOIN
    sys.extended_properties ex
ON
    ex.major_id = c.object_id
    AND ex.minor_id = c.column_id
    AND ex.name = 'MS_Description'
WHERE
    OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0
    -- AND OBJECT_NAME(c.object_id) = 'your_table'
ORDER
    BY OBJECT_NAME(c.object_id), c.column_id;


(And I hesitate to suggest it, but this is not the best way to document your
database, if that is the intention.)




Show quoteHide quote

DISTINCT returns duplicates
Local MDF Files--can I make this work?
Shrink database?
Problem with Grouping
Top 10 clients per case type & country