C# - How to I get the MS_Description field from a table schema?

string SQL = "SELECT TOP 1 * FROM " + TableName;
IDataReader reader = DatabaseClassCreator.ExecSQLReader("", SQL);
DataTable schema = re开发者_如何学Cader.GetSchemaTable();

I got the above to read information about the fields in a table of my SQL Server 2000 databases. This works but I can't seem to find a way to get the MS_Description property for a field from the schema.

How would I go about getting the field's MS_Description property?

I slightly changed it, and now it's working for me.

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

In order to get the description data you would have to query via INFORMATION_SCHEMA.COLUMNS / sysproperties. The following SQL query will work on SQL Server 2000

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 
    sysproperties s 
ON 
    s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME) 
    AND s.smallid = 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

Source: ARCHIVE - ASP FAQs How do I show the description property of a column?

Rod's query, for some reason, showed descriptions that did not correspond to the correct columns. I tried running the following query, and it seems to work better:

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
    AND ep.name = 'MS_Description'

Source: MSDN - Viewing Extended Properties

Here's a general solution that returns all the descriptions for tables and columns. The description for the table has a column id of zero.

SELECT t.Name TableName, c.column_id, c.name ColumnName, ep.value MS_Description
    FROM sys.objects AS T 
        JOIN sys.columns AS c ON T.object_id = c.object_id
        JOIN sys.extended_properties ep on ep.major_id = T.object_id AND ep.minor_id = c.column_id
    WHERE T.Type = 'U' and ep.name = 'MS_Description'
    UNION ALL
    SELECT  T.name, 0, null, ep.value
    FROM sys.tables T
        JOIN sys.extended_properties ep on T.object_id = ep.major_id AND ep.minor_id = 0
    WHERE T.Type = 'U' and ep.name = 'MS_Description'
ORDER BY t.name, column_id