Un cas curieux d’incompatibilité de collation…

Voulant réaliser une requête donnant la liste de toutes les références d’intégrité relationnelle des clés étrangères (FOREIGN KEY) relatives aux contraintes de clé primaires ou unique, je lance cette requête :

SELECT FK.TABLE_SCHEMA + '.' + FK.TABLE_NAME AS CHILD_TABLE_FULL_NAME,
       FK.CONSTRAINT_SCHEMA + '.' + FK.CONSTRAINT_NAME AS FOREIGN_KEY_FULL_NAME,
       STRING_AGG(FKC.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY FKC.ORDINAL_POSITION) AS FOREIGN_COLUMNS,
       STRING_AGG(UKC.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY UKC.ORDINAL_POSITION) AS REFERENCE_COLUMNS,
       UK.CONSTRAINT_SCHEMA + '.' + UK.CONSTRAINT_NAME AS REFERENCE_CONSTRAINT_FULL_NAME,
       UK.TABLE_SCHEMA + '.' + UK.TABLE_NAME AS REFERENCE_TABLE_FULL_NAME,
       UK.CONSTRAINT_TYPE
FROM   INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS REF 
       JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK 
          ON FK.CONSTRAINT_SCHEMA = REF.CONSTRAINT_SCHEMA AND FK.CONSTRAINT_NAME = REF.CONSTRAINT_NAME
       JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS UK 
          ON UK.CONSTRAINT_SCHEMA = REF.UNIQUE_CONSTRAINT_SCHEMA AND UK.CONSTRAINT_NAME = REF.UNIQUE_CONSTRAINT_NAME
       JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS FKC 
          ON FK.CONSTRAINT_SCHEMA = FKC.CONSTRAINT_SCHEMA AND FK.CONSTRAINT_NAME = FKC.CONSTRAINT_NAME
       JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS UKC 
          ON UK.CONSTRAINT_SCHEMA = UKC.CONSTRAINT_SCHEMA AND UK.CONSTRAINT_NAME = UKC.CONSTRAINT_NAME
GROUP  BY FK.TABLE_SCHEMA, FK.TABLE_NAME,
       FK.CONSTRAINT_SCHEMA, FK.CONSTRAINT_NAME,
       UK.CONSTRAINT_SCHEMA, UK.CONSTRAINT_NAME,
       UK.TABLE_SCHEMA, UK.TABLE_NAME, UK.CONSTRAINT_TYPE;

Et cela me lance un joyeux message d’erreur :

Msg 8711, Niveau 16, État 1, Ligne 39
Plusieurs fonctions d’agrégation ordonnées dans la même étendue ont des classements mutuellement incompatibles.

En fait la présence des deux fonctions STRING_AGG ne lui plait pas ! Bizarre… En scindant en deux la requête à l’aide d’une CTE pour finalement recoller les morceaux, je trouve la solution à mon problème…

WITH 
T1 AS
(
SELECT ROW_NUMBER() OVER(ORDER BY FK.CONSTRAINT_SCHEMA, FK.CONSTRAINT_NAME) AS RN,
       FK.TABLE_SCHEMA + '.' + FK.TABLE_NAME AS CHILD_TABLE_FULL_NAME,
       FK.CONSTRAINT_SCHEMA + '.' + FK.CONSTRAINT_NAME AS FOREIGN_KEY_FULL_NAME,
       STRING_AGG(FKC.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY FKC.ORDINAL_POSITION) AS FOREIGN_COLUMNS
FROM   INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS REF 
       JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK 
          ON FK.CONSTRAINT_SCHEMA = REF.CONSTRAINT_SCHEMA AND FK.CONSTRAINT_NAME = REF.CONSTRAINT_NAME
       JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS FKC 
          ON FK.CONSTRAINT_SCHEMA = FKC.CONSTRAINT_SCHEMA AND FK.CONSTRAINT_NAME = FKC.CONSTRAINT_NAME
GROUP  BY FK.TABLE_SCHEMA, FK.TABLE_NAME,
       FK.CONSTRAINT_SCHEMA, FK.CONSTRAINT_NAME
),
T2 AS
(
SELECT ROW_NUMBER() OVER(ORDER BY FK.CONSTRAINT_SCHEMA, FK.CONSTRAINT_NAME) AS RN,
       STRING_AGG(UKC.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY UKC.ORDINAL_POSITION) AS REFERENCE_COLUMNS,
       UK.CONSTRAINT_SCHEMA + '.' + UK.CONSTRAINT_NAME AS REFERENCE_CONSTRAINT_FULL_NAME,
       UK.TABLE_SCHEMA + '.' + UK.TABLE_NAME AS REFERENCE_TABLE_FULL_NAME,
       UK.CONSTRAINT_TYPE AS UNIQUE_CONSTRAINT_TYPE_REF
FROM   INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS REF 
       JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK 
          ON FK.CONSTRAINT_SCHEMA = REF.CONSTRAINT_SCHEMA AND FK.CONSTRAINT_NAME = REF.CONSTRAINT_NAME
       JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS UK 
          ON UK.CONSTRAINT_SCHEMA = REF.UNIQUE_CONSTRAINT_SCHEMA AND UK.CONSTRAINT_NAME = REF.UNIQUE_CONSTRAINT_NAME
       JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS UKC 
          ON UK.CONSTRAINT_SCHEMA = UKC.CONSTRAINT_SCHEMA AND UK.CONSTRAINT_NAME = UKC.CONSTRAINT_NAME
GROUP  BY UK.CONSTRAINT_SCHEMA, UK.CONSTRAINT_NAME, UK.CONSTRAINT_TYPE,
       UK.TABLE_SCHEMA, UK.TABLE_NAME, FK.CONSTRAINT_SCHEMA, FK.CONSTRAINT_NAME
)
SELECT CHILD_TABLE_FULL_NAME, FOREIGN_KEY_FULL_NAME, FOREIGN_COLUMNS, REFERENCE_COLUMNS,
       REFERENCE_CONSTRAINT_FULL_NAME, REFERENCE_TABLE_FULL_NAME, UNIQUE_CONSTRAINT_TYPE_REF
FROM   T1 JOIN T2 ON T1.RN = T2.RN;

Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *