SQL Server: Extract Table Meta-Data (description, fields and their data types)

Question

I am trying to find a way to extract information about my tables in SQL Server (2008).
The data I need needs to include the description of the table (filled from the Description property in the Properties Window), a list of fields of that table and their respective data types.

Is there any way I can extract such meta-data? I presume I have to use some sys sp but I'n not sure which one.

Answer 1

To get the description data, you unfortunately have to use sysobjects/syscolumns to get the ids:

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() - so what would be the point?