CODEWITHSUNDEEP
asp.netsql-server-2005
Pablo
Pablo

Reputation: 5087

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?

Upvotes: 0

Views: 1021

Answers (3)

Raja
Raja

Reputation: 3618

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

Upvotes: 2

marc_s
marc_s

Reputation: 754538

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

Upvotes: 0

KM.
KM.

Reputation: 103597

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

Upvotes: 1

Related Questions