Voici une procédure destinée à générer un script SQL de création ou de suppression des contraintes d’intégrité référentielle d’une base de données.

CREATE PROCEDURE dbo.P_DDL_FK_CONSTRAINTS @SQLCOMMAND VARCHAR(4) 

AS 

 

/******************************************************************************   

* Procédure de génération d'ordre SQL DDL de création ou suppression          * 

* de contrainte d'intégrité référentielle FOREIGN KEY                         *  

*******************************************************************************   

* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-09-26 *   

******************************************************************************/  

 

BEGIN 

 

-- vérification du paramètre de la procédure :  

--    DROP => script de suppression, ADD => script de génération 

IF @SQLCOMMAND NOT IN ('ADD', 'DROP') 

   RETURN; 

 

-- variables locales 

DECLARE @SQL VARCHAR(MAX), @COLS_FK VARCHAR(MAX), @COLS_UK VARCHAR(MAX), 

        @TABLE_SCHEMA sysname, @TABLE_NAME sysname,  

        @CONSTRAINT_SCHEMA sysname, @CONSTRAINT_NAME sysname,  

        @MATCH_OPTION NVARCHAR(7),  

        @UPDATE_RULE NVARCHAR(11), @DELETE_RULE NVARCHAR(11), 

        @U_TABLE_SCHEMA sysname, @U_TABLE_NAME sysname,  

        @U_CONSTRAINT_NAME sysname, @U_CONSTRAINT_SCHEMA sysname; 

 

-- curseur sur les contraintes 

DECLARE C CURSOR 

FOR  

   SELECT TC.TABLE_SCHEMA, TC.TABLE_NAME, RC.CONSTRAINT_SCHEMA,  

          RC.CONSTRAINT_NAME, MATCH_OPTION, UPDATE_RULE, DELETE_RULE, 

          TU.TABLE_SCHEMA AS U_TABLE_SCHEMA, TU.TABLE_NAME AS U_TABLE_NAME, 

          TU.CONSTRAINT_SCHEMA AS U_CONSTRAINT_SCHEMA,   

          TU.CONSTRAINT_NAME AS U_CONSTRAINT_NAME 

   FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC 

          INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS  AS RC 

                ON RC.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA 

                   AND RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME 

          INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TU 

                ON RC.UNIQUE_CONSTRAINT_SCHEMA = TU.CONSTRAINT_SCHEMA 

                   AND RC.UNIQUE_CONSTRAINT_NAME = TU.CONSTRAINT_NAME 

FOR READ ONLY; 

 

-- ouverture du curseur 

OPEN C; 

 

-- lecture ligne 

FETCH C INTO @TABLE_SCHEMA, @TABLE_NAME,  

             @CONSTRAINT_SCHEMA, @CONSTRAINT_NAME,  

             @MATCH_OPTION, @UPDATE_RULE, @DELETE_RULE,  

             @U_TABLE_SCHEMA, @U_TABLE_NAME, 

             @U_CONSTRAINT_SCHEMA, @U_CONSTRAINT_NAME; 

 

-- boucle de lecture 

WHILE @@FETCH_STATUS = 0 

BEGIN 

 

   SET @COLS_FK = ''; 

 

   -- concaténation des colonnes composant la FOREIGN KEY 

   SELECT @COLS_FK = @COLS_FK + COLUMN_NAME + ', ' 

   FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE 

   WHERE  TABLE_SCHEMA =      @TABLE_SCHEMA 

     AND  TABLE_NAME  =       @TABLE_NAME 

     AND  CONSTRAINT_SCHEMA = @CONSTRAINT_SCHEMA 

     AND  CONSTRAINT_NAME =   @CONSTRAINT_NAME; 

 

   SET @COLS_FK = SUBSTRING(@COLS_FK, 1, LEN(@COLS_FK) -1); 

 

   SET @COLS_UK = ''; 

 

   -- concaténation des colonnes composant la PRIMARY KEY ou la contrainte UNIQUE 

   -- sur laquelle la contrainte FOREIGN KEY est greffée 

   SELECT @COLS_UK = @COLS_UK + COLUMN_NAME + ', ' 

   FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE 

   WHERE  TABLE_SCHEMA =      @U_TABLE_SCHEMA 

     AND  TABLE_NAME  =       @U_TABLE_NAME 

     AND  CONSTRAINT_SCHEMA = @U_CONSTRAINT_SCHEMA 

     AND  CONSTRAINT_NAME =   @U_CONSTRAINT_NAME; 

 

   SET @COLS_UK = SUBSTRING(@COLS_UK, 1, LEN(@COLS_UK) -1); 

 

   -- composition de l'ordre SQL de génération ou  suppression de la contrainte 

   SET @SQL = 'ALTER TABLE [' + @TABLE_SCHEMA + '].[' + @TABLE_NAME +'] ' 

              + CASE WHEN @SQLCOMMAND = 'DROP' THEN 'DROP CONSTRAINT [' + @CONSTRAINT_NAME +'];' 

                     ELSE 'ADD CONSTRAINT [' + @CONSTRAINT_NAME +'] FOREIGN KEY (' + @COLS_FK  

                           +') REFERENCES ['+ @U_TABLE_SCHEMA + '].[' + @U_TABLE_NAME  

                           + '] (' + @COLS_UK + ') MATCH ' + @MATCH_OPTION  

                           + ' ON UPDATE ' + @UPDATE_RULE + ' ON DELETE ' + @DELETE_RULE +';' 

                END; 

 

   -- sortie 

   PRINT @SQL; 

 

   -- lecture ligne suivante 

   FETCH C INTO @TABLE_SCHEMA, @TABLE_NAME,  

                @CONSTRAINT_SCHEMA, @CONSTRAINT_NAME,  

                @MATCH_OPTION, @UPDATE_RULE, @DELETE_RULE,  

                @U_TABLE_SCHEMA, @U_TABLE_NAME, 

                @U_CONSTRAINT_SCHEMA, @U_CONSTRAINT_NAME; 

END 

 

-- fermeture du curseur 

CLOSE C; 

 

DEALLOCATE C; 

 

END;

Pour utiliser cette procédure, par exemple pour générer les contraintes, exécutez la procédure comme suit :

EXECUTE dbo.P_DDL_FK_CONSTRAINTS 'ADD';

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