I made a select using metadata, but the result of my selection brought me something like a 'Cross Join'. Even using GROUP BY and DISTINCT returns with the duplicate values. I would like a help for the values to return correctly ... follows the sql:
SQL:
SELECT distinct
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)
TEP.value as 'Descrição'
FROM sys.columns C
INNER JOIN sys.tables T
ON T.object_id = C.object_id
LEFT JOIN sys.schemas S
ON T.schema_id = S.schema_id
INNER JOIN sys.types TY
ON TY.user_type_id = C.user_type_id
LEFT JOIN sys.extended_properties TEP
ON T.object_id = TEP.major_id
Image of the result:
IthinktheproblemisofthelastJOIN,whenIchangetheparameter'TEP.major_id'to'TEP.minor_id'itreturnsthecorrectvalues,butwithoutthedescriptionsofthecolumns.
Imageoftheresult(using'TEP.minor_id'):