Extracting SQL Server table and field descriptions with SQL

Have you ever wanted to extract the table descriptions and/or field descriptions from an SQL Server database?

I found some solutions on StackExchange, but all of those solutions tried to combine data from the global system tables with data from the database's system tables.  The two down sides to this were:
(1) the global system tables didn't contain all of the table and field names, so there were no IDs to match some of the database's system tables to, and
(2) in my scenario I was not able to use a USE "database name" to change the context.

I needed a solution that only used the database's system tables.  So I got familiar with the system tables and wrote one.

Without further ado, that solution is this:

select t.name AS [table name], c.name AS [column name], p.value
from "DBNAME".sys.tables AS t
inner join "DBNAME".sys.columns AS c ON t.object_id = c.object_id
inner join "DBNAME".sys.extended_properties p on p.major_id = t.object_id and p.minor_id = c.column_id
where t.type_desc = 'USER_TABLE'
union
select t.name AS [table name], '', p.value
from "DBNAME".sys.tables AS t
inner join "DBNAME".sys.extended_properties p on p.major_id = t.object_id and p.minor_id = 0
order by 1, 2

Just replace "DBNAME" with the name of the database you want to use, and you'll get a nicely formatted list of both table and field comments.  If you wanted to exclude the table names on the field comments, you could do some control break processing, or get creative in Excel.  Hope this helps you out!