retrieving description item from sql server table column

is it possible to retrieve by using connection.getschema() the description item from a sql server table column, just like it's possible to retrieve the column name, data type, is nullable, column default value, etc? if so, how?

Answers


On Sql Server 2005, you can use this system table value function:

fn_listextendedproperty (Transact-SQL)

or try a query, from from this article, like this:

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

Try this:

    SELECT 
    [Table Name] = i_s.TABLE_NAME, 
    [Column Name] = i_s.COLUMN_NAME, 
    [Description] = s.value 
FROM 
    INFORMATION_SCHEMA.COLUMNS i_s 
LEFT OUTER JOIN 
    sys.extended_properties s 
ON 
    s.major_id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME) 
    AND s.minor_id = i_s.ORDINAL_POSITION 
    AND s.name = 'MS_Description' 
WHERE 
    OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0 
    --AND i_s.TABLE_NAME = 'table_name' 
ORDER BY 
    i_s.TABLE_NAME, i_s.ORDINAL_POSITION

edit: fixed the query :-)

HTH


If you already have the DataTable, as you mention - look at its data columns!

foreach(DataColumn col in dataTable.Columns)
{
  // check out all the properties on the DataColumn
}

Does that contain what you need to have??


Need Your Help

How to link a Visual Basic 6 Combo box with a field in a Microsoft Access 2007 Table

ms-access vb6 combobox database-connection

There is a Teacher Table in Microsoft Access 2007. And i want to get all the names of the Teachers from the field Tch_name to a combo box list in VB6. Does a loop needs to be created? Can anyone he...

Http Status Code 301 to a page that return 410

seo http-status-codes http-status-code-410

I have been asked by a client to return a 410 Status code for some URLs in a previous version of there site.