Article complet: Trois petites fonctions de calcul de jointures

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...

[Suite:]

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 * * * * *