Accessing table comments in SQL Server

Question

I am migrating a database from Oracle to SQL Server. In Oracle one can read table comments using the all_tab_comments table. Can't find a way of doing this in SQL Server. How can one handle this?

Answer 1

select  t.id                        as  "object_id",
        t.name                      as  "TableName",
        schema_name(t2.schema_id)   as  "Schema",
        t3.name                     as  "Column",
        t4.value                    as  "Column description",
        t5.value                    as  "Table description"
from    sysobjects t
inner join sys.tables t2 on t2.object_id = t.id
inner join sys.columns t3 on t3.object_id = t.id
left join sys.extended_properties t4 on t4.major_id = t.id
                                        and t4.name = 'MS_Description'
                                        and t4.minor_id = t3.column_id
left join sys.extended_properties t5 on t5.major_id = t.id
                                        and t5.name = 'MS_Description'
                                        and t5.minor_id = 0
where   t.name = 'YourTableName'
        and t2.schema_id = schema_id('YourSchemaName')

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