Liste des clefs étrangères avec correspondances des colonnes référencées

Voici une requête permettant de retrouver dans vos bases l’ensemble des clefs étrangères et leur articulation :
table mère, table fille, jointure entre mère et fille, définition de la clef mère et de la clef fille ainsi que les paramètres de la clef étrangère (option « MATCH » et règle ON UPDATE/DELETE).

SELECT DISTINCT TCU.TABLE_SCHEMA AS SCH_RF, TCU.TABLE_NAME AS TBL_RF,
       TCF.TABLE_SCHEMA AS SCH_FK, TCF.TABLE_NAME AS TBL_FK,
       STUFF((SELECT N' AND RF.[' + KCUU.COLUMN_NAME + '] = FK.[' + KCUF.COLUMN_NAME +']'
              FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCUU
                     JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCUF
                          ON KCUU.ORDINAL_POSITION = KCUF.ORDINAL_POSITION
              WHERE  RC.UNIQUE_CONSTRAINT_SCHEMA = KCUU.CONSTRAINT_SCHEMA AND
                     RC.UNIQUE_CONSTRAINT_NAME   = KCUU.CONSTRAINT_NAME AND
                     RC.CONSTRAINT_SCHEMA = KCUF.CONSTRAINT_SCHEMA AND
                     RC.CONSTRAINT_NAME   = KCUF.CONSTRAINT_NAME
              FOR XML PATH(N'')), 1, 5, N'') AS JOIN_RFK,
       STUFF((SELECT N', [' + KCUU.COLUMN_NAME + ']'
              FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCUU
              WHERE  RC.UNIQUE_CONSTRAINT_SCHEMA = KCUU.CONSTRAINT_SCHEMA AND
                     RC.UNIQUE_CONSTRAINT_NAME   = KCUU.CONSTRAINT_NAME
              FOR XML PATH(N'')), 1, 2, N'(') + ')' AS KEY_RF,
       STUFF((SELECT N', [' + KCUF.COLUMN_NAME + ']'
              FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCUF
              WHERE  RC.CONSTRAINT_SCHEMA = KCUF.CONSTRAINT_SCHEMA AND
                     RC.CONSTRAINT_NAME   = KCUF.CONSTRAINT_NAME
              FOR XML PATH(N'')), 1, 2, N'(') + ')' AS KEY_FK,
       RC.MATCH_OPTION, RC.DELETE_RULE, RC.UPDATE_RULE
FROM   INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
       JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TCU
            ON RC.UNIQUE_CONSTRAINT_SCHEMA = TCU.CONSTRAINT_SCHEMA AND
               RC.UNIQUE_CONSTRAINT_NAME   = TCU.CONSTRAINT_NAME
       JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCUU
            ON RC.UNIQUE_CONSTRAINT_SCHEMA = KCUU.CONSTRAINT_SCHEMA AND
               RC.UNIQUE_CONSTRAINT_NAME   = KCUU.CONSTRAINT_NAME
       JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TCF
            ON RC.CONSTRAINT_SCHEMA = TCF.CONSTRAINT_SCHEMA AND
               RC.CONSTRAINT_NAME   = TCF.CONSTRAINT_NAME
       JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCUF
                 ON RC.CONSTRAINT_SCHEMA = KCUF.CONSTRAINT_SCHEMA AND
                    RC.CONSTRAINT_NAME   = KCUF.CONSTRAINT_NAME

Cette requête utilise les vues normalisées d’information de schéma.

Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES
Expert  S.G.B.D  relationnelles   et   langage  S.Q.L
Moste  Valuable  Professionnal  Microsoft  SQL Server
Société SQLspot  :  modélisation, conseil, formation,
optimisation,  audit,  tuning,  administration  SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.

L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR

MVP Microsoft SQL
Server

Développez et administrez pour la performance avec SQL Server 2014

Développez et administrez pour la performance avec SQL Server 2014

Laisser un commentaire