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
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.
LE CODE ! LE CODE ! LE CODE ! LE CODE ! LE CODE ! LE CODE ! LE CODE ! LE CODE ! LE CODE ! LE CODE !
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.
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
Développez et administrez pour la performance avec SQL Server 2014