Voici trois petites fonctions pour ceux qui utilisent du SQL dynamique, afin de réaliser de manière automatique différentes jointures entre deux tables…
1 – Jointure naturelle
La première consiste à effectuer la jointure naturelle, c’est à dire à joindre les deux tables sur les colonnes ayant même nom. Rappelons que dans un système d’information, les informations doivent avoir un nom unique (norme AFNOR).
CREATE FUNCTION dbo.F_SCRIPT_NATURAL_JOIN
(@SHEMA_LEFT SYSNAME, @TABLE_LEFT SYSNAME, @ALIAS_LEFT SYSNAME,
@SHEMA_RIGHT SYSNAME, @TABLE_RIGHT SYSNAME, @ALIAS_RIGHT SYSNAME)
RETURNS VARCHAR(max)
AS
/******************************************************************************
* fonction de calcul de jointure naturelle *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-04-10 *
*******************************************************************************
* Cette fonction calcule la jointure naturelle de deux tables, *
******************************************************************************/
BEGIN
DECLARE @OUT VARCHAR(max);
SET @OUT = '';
SELECT @OUT = @OUT + @ALIAS_LEFT +'.' + COLUMN_NAME +' = ' + @ALIAS_RIGHT + '.' + COLUMN_NAME + ' AND '
FROM (SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SHEMA_LEFT
AND TABLE_NAME = @TABLE_LEFT
INTERSECT
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SHEMA_RIGHT
AND TABLE_NAME = @TABLE_RIGHT) AS T;
IF LEN(@OUT) > 4
SET @OUT = SUBSTRING(@OUT, 1, LEN(@OUT) - 4);
RETURN @OUT;
END
GO
2 – Auto jointure
Il s’agit de joindre la table sur un clone de cette même table (par exemple avec un des pseudo table inserted ou deleted) c’est à dire de clef à clef.
CREATE FUNCTION dbo.F_SCRIPT_SELF_JOIN
(@SHEMA SYSNAME, @TABLE SYSNAME, @ALIAS_LEFT SYSNAME, @ALIAS_RIGHT SYSNAME)
RETURNS VARCHAR(max)
AS
/******************************************************************************
* fonction de calcul d'auto jointure *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-04-10 *
*******************************************************************************
* Cette fonction calcule la jointure naturelle de deux tables, *
******************************************************************************/
BEGIN
DECLARE @OUT VARCHAR(max);
SET @OUT = '';
SELECT @OUT = @OUT + @ALIAS_LEFT +'.' + COLUMN_NAME +' = ' + @ALIAS_RIGHT + '.' + COLUMN_NAME + ' AND '
FROM (SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE TC.TABLE_SCHEMA = @SHEMA
AND TC.TABLE_NAME = @TABLE
AND CONSTRAINT_TYPE = 'PRIMARY KEY') AS T;
IF LEN(@OUT) > 4
SET @OUT = SUBSTRING(@OUT, 1, LEN(@OUT) - 4);
RETURN @OUT;
END
GO
3 РJointure r̩f̩rentielle
Cette troisième fonction permet de réaliser la jointure d’une table fille avec sa table mère liée par l’intégrité référentielle.
CREATE FUNCTION dbo.F_SCRIPT_REF_JOIN
(@SHEMA_MERE SYSNAME, @TABLE_MERE SYSNAME, @ALIAS_LEFT SYSNAME,
@SHEMA_FILLE SYSNAME, @TABLE_FILLE SYSNAME, @ALIAS_RIGHT SYSNAME)
RETURNS VARCHAR(max)
AS
/******************************************************************************
* fonction de calculs de jointure référentielle *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-04-10 *
*******************************************************************************
* Cette fonction calcule la jointure référentielle entre deux tables, l'une *
* mère l'autre fille *
******************************************************************************/
BEGIN
DECLARE @OUT VARCHAR(max);
SET @OUT = '';
SELECT @OUT = @OUT + @ALIAS_LEFT +'.' + COL_MERE +' = ' + @ALIAS_RIGHT + '.' + COL_FILLE + ' AND '
FROM (SELECT KCU_PRIMARY.COLUMN_NAME AS COL_MERE,
KCU_FOREIGN.COLUMN_NAME AS COL_FILLE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC_PRIMARY
ON RC.UNIQUE_CONSTRAINT_SCHEMA = TC_PRIMARY.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = TC_PRIMARY.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC_FOREIGN
ON RC.CONSTRAINT_SCHEMA = TC_FOREIGN.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = TC_FOREIGN.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU_PRIMARY
ON TC_PRIMARY.CONSTRAINT_SCHEMA = KCU_PRIMARY.CONSTRAINT_SCHEMA
AND TC_PRIMARY.CONSTRAINT_NAME = KCU_PRIMARY.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU_FOREIGN
ON TC_FOREIGN.CONSTRAINT_SCHEMA = KCU_FOREIGN.CONSTRAINT_SCHEMA
AND TC_FOREIGN.CONSTRAINT_NAME = KCU_FOREIGN.CONSTRAINT_NAME
WHERE TC_PRIMARY.TABLE_SCHEMA = @SHEMA_MERE
AND TC_PRIMARY.TABLE_NAME = @TABLE_MERE
AND TC_FOREIGN.TABLE_SCHEMA = @SHEMA_FILLE
AND TC_FOREIGN.TABLE_NAME = @TABLE_FILLE
AND KCU_PRIMARY.ORDINAL_POSITION = KCU_FOREIGN.ORDINAL_POSITION ) AS T;
IF LEN(@OUT) > 4
SET @OUT = SUBSTRING(@OUT, 1, LEN(@OUT) - 4);
RETURN @OUT;
END
GO
—
Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l’ISEN à Toulon * * * * *