SQL Server Extract Table Meta-Data description fields and their data types

Question

I'm looking for a technique to retrieve data from SQL Server about my tables (2008). The information I require must include the table's description (completed from the Description property in the Properties Window), a list of the table's fields, and a description of the various data types for each field.

Is there a way for me to obtain this meta information? I assume I must utilize a system service, but I'm not sure which one.

Answer 1

Unfortunately, you have to utilize sysobjects/syscolumns to retrieve the ids in order to get the description data:

SELECT      u.name + '.' + t.name AS [table],
            td.value AS [table_desc],
            c.name AS [column],
            cd.value AS [column_desc]
FROM        sysobjects t
INNER JOIN  sysusers u
    ON      u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON      td.major_id = t.id
    AND     td.minor_id = 0
    AND     td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON      c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON      cd.major_id = c.id
    AND     cd.minor_id = c.colid
    AND     cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY    t.name, c.colorder

You can do it with info-schema, but you'd have to concatenate etc to call OBJECT_ID()