Get SQL Server's constraints list without redundancy

Question

With this query I get the list of all my database's CHECK, FOREIGN_KEY, PRIMARY_KEY and UNIQUE_KEY constraints.

SELECT 
    o.object_id as ID, o.name AS Name, 
    OBJECT_NAME(o.parent_object_id) AS TableName, 
    o.type_desc AS TypeName, 
    cs.COLUMN_NAME as ColumnName
FROM 
    sys.objects o 
LEFT JOIN 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cs ON o.name = cs.CONSTRAINT_NAME 
WHERE 
    o.type = 'C' or o.type = 'F' or o.type = 'PK' or o.type = 'UQ'

However, there are some of them with many 'ColumnName' and I want to concat this.

For example :

  'PK_ENTITE_SIGN_DOSSIER_ID_DOSSIER_ID_ENTITE_ID_GROUPE_SIGN_ID_PERSONNE_ID_SCHEMA'

is a PRIMARY_KEY on table ENTITE_SIGN_DOSSIER and contains ID_DOSSIER, ID_ENTITE, ID_GROUPE_SIGN, ID_PERSONNE and ID_SCHEMA (5 columns) and in this case, my query return 5 lines for this constraint.

How can I concat those columns name on the query's result please ?

Thanks a lot for your help

Answer 1

This is standard xml and stuff function trick:

SELECT  o.object_id AS ID ,
        o.name AS Name ,
        OBJECT_NAME(o.parent_object_id) AS TableName ,
        o.type_desc AS TypeName ,
        ca.ColumnName
FROM    sys.objects o
        CROSS APPLY ( SELECT    STUFF(( SELECT  ', ' + cs.COLUMN_NAME
                                        FROM    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cs
                                        WHERE   o.name = cs.CONSTRAINT_NAME
                                      FOR
                                        XML PATH('')
                                      ), 1, 2, '') AS ColumnName
                    ) ca
WHERE   o.type = 'C'
        OR o.type = 'F'
        OR o.type = 'PK'
        OR o.type = 'UQ'
ORDER BY ID