Show table comments

There are some ways to get the extended properties of a table. One of them is through the function fn_listextendedproperty () :

-- código #1
SELECT sys.objects.name as [Nome da tabela], 
       EP.name as [Nome do comentário], 
       EP.value as [Descrição do comentário]
  from sys.objects
  cross apply fn_listextendedproperty(default,
                                    'SCHEMA', schema_name(schema_id),
                                    'TABLE', name, null, null) as EP
  where sys.objects.name not in ('sysdiagrams');

The above code lists all of the extended properties. If you want to limit to a specific property, add the filter in the WHERE clause.

In the Query to select the description of a column it is also used, but to get a description of the columns.

You have to inspect the extended_properties system table

See the example:

CREATE table Tabela (id int , campo char (20))

EXEC   sp_addextendedproperty 'Descrição', 'ID', 'user', dbo, 'table', 'Tabela', 'column', id

EXEC   sp_addextendedproperty 'Descrição', 'Campo', 'user', dbo, 'table', 'Tabela', 'column', campo

To list the comments just do:

select * 
from sys.extended_properties 
where NAME = 'Descrição'

See working in SQLFiddle

Check the documentation for the table Sys.extended_properties for details.

Here is the script I did and returned:

select ob.name,ep.value from sys.extended_properties ep
 inner join sys.objects ob ON ep.major_id=ob.OBJECT_ID AND class=1
 where minor_id = 0 and ob.name not like '%sp%'