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