SQL Server 2017 - SELECT with Duplicate Metadata

Asked

Viewed 111 times

0

I made a select using metadata, but the result of my selection brought me something like 'Cross Join'. Even using GROUP BY and DISTINCT returns with duplicate values. I would like an help to return the values correctly ... follows 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


Result image:

Imagem de explicação:

I think the problem is from the last JOIN, when I change the parameter 'TEP.major_id' to 'TEP.minor_id' it returns the correct values, but without the descriptions of the columns.

Screenshot of the result (using 'TEP.minor_id'):

imagem 2

sql sql-server select-sql

  • Not duplicated because the values in the last column are different. One of the ways to solve this is by making a select of this select by filtering only what you want. SELECT * FROM ( seu_select ) A WHERE DESCRIPTION = 'o_que_desejar'

1 answer

0

It seems to me that the junction with the exhibition sys.extended_properties is incomplete. Try the following:

-- código #1
....
LEFT JOIN sys.extended_properties TEP 
     ON T.object_id = TEP.major_id
        and C.object_id = TEP.minor_id
        and TEP.name = 'MS_Description'

I didn’t test it, but I hope this is it.

Browser other questions tagged sql sql-server select-sql

You are not signed in. Login or sign up in order to post.