A partir d’un post dans le forum SQL Server demandant comment mettre un place un système de diagnostic de cohérence d’une base de données (débosselage), je me suis mis en frais pour penser la chose et la faire !!!

Voici la demande telle qu’elle est parue :

Mettre en place un « autodiagnostic » de l’état de santé des données de la base SQL Server.
Un ensemble de scripts sql vont être défini qui permettront de savoir à tout moment si la base n’est pas corrompu.
Ce jeu de script sera exécuté en automatique de manière régulière et une alerte sera levée au support […] si une règle retourne une ligne (une erreur).

– Pour un même client il n’existe pas 2 idEmpruntClient identiques.
– On peut vérifier que les date des échéances sont bien inclues dans les bornes de sa phase…

Et voici ma réponse :

il faut créer pour chaque règle une requête qui, s’il y a une incohérence, va retourner au moins une ligne.
1) Mettre toutes ces requêtes dans une table des requêtes. (T_QUERY_QRY par exemple)
2) Planifier la vérification à l’aide de l’agent SQL
3) Mettre le résultat dans une table d’éxécution s’il y an as un (T_VERIFY_VRF par exemple)

Pour l’exécution, une procédure stockée suffit….

Voici comment je modéliserait la chose :

1 – tables

CREATE SCHEMA S_DIAG 

 

CREATE TABLE T_QUERY_QRY 

(QRY_ID                 INT NOT NULL PRIMARY KEY IDENTITY, 

 QRY_REGLE              VARCHAR(2048) NOT NULL, 

 QRY_QUERY              VARCHAR(max)) 

 

CREATE TABLE T_VERIFY_VRF 

(VRF_ID                 INT NOT NULL PRIMARY KEY IDENTITY, 

 VRF_DATE               DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  

 QRY_ID                 INT NOT NULL FOREIGN KEY REFERENCES T_QUERY_QRY (QRY_ID), 

 VRF_RESULT             XML);

2 Рproc̩dure pour v̩rification :

CREATE PROCEDURE S_DIAG.P_VERIFY 

AS 

 

-- variables de travail : 

DECLARE @QRY_ID INT,                   -- id requête de vérification 

        @QRY_QUERY VARCHAR(max),       -- texte SQL requête de vérification 

        @SQL VARCHAR(max),             -- texte SQL de la requête d'exécution encapsulant la requête de vérification 

        @DT  DATETIME;                 -- date heure d'exécution 

         

DECLARE @T TABLE (C XML);              -- variable table intermédiaire de travail         

          

SET @DT = CURRENT_TIMESTAMP;           -- assignation date heure   

 

DECLARE C CURSOR                       -- déclaration curseur parcourant les règles 

LOCAL FORWARD_ONLY STATIC READ_ONLY 

FOR  

   SELECT QRY_ID, QRY_QUERY 

   FROM   S_DIAG.T_QUERY_QRY; 

    

OPEN C;                                -- ouverture curseur    

 

FETCH C INTO @QRY_ID, @QRY_QUERY;      -- lecture ligne courante dans le curseur 

 

WHILE @@FETCH_STATUS = 0               -- boucle jusqu'à fin de ligne de table  

BEGIN 

 

    DELETE FROM @T;                    -- vide table de travail 

     

    SET @QRY_QUERY =                   -- construction dynamique de la requête à exécuter 

        'SELECT CAST((SELECT *  

         FROM   (' + @QRY_QUERY + ') AS T 

         FOR XML AUTO, ELEMENTS XSINIL, ROOT(''DIAGNOSTIC''), BINARY BASE64) AS XML) AS X'     

   

    INSERT INTO @T                     -- insertion résultat.... 

    EXEC (@QRY_QUERY);                 -- ... d'éxécution dynamique dans table de travail 

                  

    INSERT INTO S_DIAG.T_VERIFY_VRF    -- insertion du contenu de la table de travail dans la table de vérification 

           (VRF_DATE, QRY_ID,  VRF_RESULT) 

    SELECT @DT,       @QRY_ID, C 

    FROM   @T 

    WHERE  C IS NOT NULL;      

 

    FETCH C INTO @QRY_ID, @QRY_QUERY;  -- lecture ligne suivante dans le curseur 

 

END; 

 

CLOSE C;                               -- fermeture curseur  

 

DEALLOCATE C;                          -- désallocation curseur 

 

SELECT VRF_DATE, QRY_REGLE, VRF_RESULT -- affichage des résultats 

FROM   S_DIAG.T_VERIFY_VRF AS V 

       INNER JOIN T_QUERY_QRY AS Q 

             ON V.QRY_ID = Q.QRY_ID 

WHERE  VRF_DATE = @DT 

ORDER  BY Q.QRY_ID;

3 – jeu d’essais :

INSERT INTO S_DIAG.T_QUERY_QRY  

VALUES ('Toutes les tables doivent avoir une clef',  

        'SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES EXCEPT SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = ''PRIMARY KEY'''); 

INSERT INTO S_DIAG.T_QUERY_QRY  

VALUES ('Rien',  

        'SELECT 1 AS N WHERE 2 = 3');

L’une peu donner des résultats, l’autre JAMAIS !

4 Рex̩cution :

EXEC S_DIAG.P_VERIFY

Aussi simple que cela !
A vous d’écrire les bonnes requêtes…

-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *