SQL Server 2017 - Retrieve column description / comment on table in database - Metadata

Navigation
0

I am trying to generate a data dictionary from my database in SQL Server 2017, and would like to retrieve the description / comment from the 'Description' field contained in the column definitions of each table. I was inspired by a code I found on a website, but I'm not able to add this table metadata.

Desired field:

SQL that I'm using: 

SELECT

S.name as 'Schema',

T.name as Tabela,

C.name as Coluna,

TY.name as Tipo,

C.max_length as 'Tamanho Máximo', -- Tamanho em bytes, para nvarchar normalmente se divide este valor por 2

C.precision as 'Precisão', -- Para tipos numeric e decimal (tamanho)

C.scale as 'Escala' -- Para tipos numeric e decimal (números após a virgula)

FROM sys.columns C

INNER JOIN sys.tables T
ON T.object_id = C.object_id

INNER JOIN sys.types TY
ON TY.user_type_id = C.user_type_id

LEFT JOIN sys.schemas S
ON T.schema_id = S.schema_id
    
asked by anonymous 05.10.2018 / 19:35

1 answer

0

The description of the column is in the table sys.extended_properties , needs to include in its select doing the join with object_id, like this: 

left join sys.extended_properties tep on T.object_id = tep.major_id

I used left join if the column does not have description . Include the "value" column in the select: 

tep.value [Description]
    
05.10.2018 / 19:42
Read file.xml in an FTP Set, fetch and check a Cookie after a video (YouTube API) finish - JavaScript