Show comments table

Question

Good morning guys,

I wonder if it is possible to list in SQL-SERVER all comments of my "table", not comments of the column, but only of the "tables""

Answer 1

There are a few 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 extended properties. If you want you can limit it to a specific property, added the filter in the WHERE clause.

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

Answer 2

You have to inspect the system table extended_properties

Take 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 on Sqlfiddle

Check the table documentation Sys.extended_properties for more details.

Answer 3

Below is the script I made 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%'