Il est possible de laisser à SQL Server le soin de gérer le versionnement de l’ensemble des objets créés dans les bases (tables, contraintes, index, vues, déclencheurs, fonctions, procédures…).
Ceci se fait à l’aide d’un déclencheur DDL, d’une table de suivi, et d’une requête astucieuse dépliant le contenu XML de EVENTDATA (le paquet d’information d’un événement DDL) agrémentée d’une fonction de fenêtrage…
La table de suivi :
GO
CREATE TABLE S_DDL.T_SUIVI_DDL
(DDL_ID BIGINT IDENTITY PRIMARY KEY,
DDL_DH datetime2(7) DEFAULT GETDATE(),
DDL_APPLICATION nvarchar(128) DEFAULT APP_NAME(),
DDL_HOST nvarchar(128) DEFAULT HOST_NAME(),
DDL_CONNEXION nvarchar(128) DEFAULT SYSTEM_USER,
DDL_USER nvarchar(128) DEFAULT USER,
DDL_DATA XML NOT NULL);
GO
Cette table est créée dans un schema SQL de nom S_DDL (pour l’isoler des tables dbo déjà présentes) et dans la base msdb qui est la base d’administration des serveur SQL à défaut.
Le déclencheur DDL de traçage des modifications :
GO
CREATE TRIGGER E_DDL_ALL
ON ALL SERVER
FOR DDL_EVENTS
AS
INSERT INTO msdb.S_DDL.T_SUIVI_DDL (DDL_DATA)
SELECT EVENTDATA();
GO
De niveau serveur, il va pister ce qui se passe dans toutes les bases du serveur et pour toute action DDL (create, alter, drop et bien d’autres choses encore !)
Un petit exemple :
GO
CREATE PROCEDURE P1
AS
SELECT 'Il est : ' + CONVERT(CHAR(8), GETDATE(), 108);
GO
ALTER PROCEDURE P1
AS
SELECT 'Il est exactement : ' + CONVERT(CHAR(8), GETDATE(), 108);
GO
ALTER PROCEDURE P1
AS
SELECT 'Il était exactement : ' + CONVERT(CHAR(8), GETDATE(), 108);
GO
DROP PROCEDURE P1
GO
CREATE PROCEDURE P1
AS
SELECT 'Il était pas loin de : ' + CONVERT(CHAR(8), GETDATE(), 108);
GO
Pour lire la table de suivi, il suffit d’une toute petite requête :
Néanmoins cette requête n’est pas très bandant du fait du XML… Extrayons les infos qui nous intéressent, sous forme atomique :
T0 AS
(
SELECT DDL_ID, DDL_DH, DDL_APPLICATION, DDL_HOST, DDL_CONNEXION, DDL_USER,
Txml.DataXml.VALUE('(./EventType[1])', 'sysname') AS EVENT_TYPE,
Txml.DataXml.VALUE('(./DatabaseName[1])', 'sysname') AS DATABASE_NAME,
Txml.DataXml.VALUE('(./SchemaName[1])', 'sysname') AS SCHEMA_NAME,
Txml.DataXml.VALUE('(./ObjectName[1])', 'sysname') AS OBJECT_NAME,
Txml.DataXml.VALUE('(./ObjectType[1])', 'sysname') AS OBJECT_TYPE,
Txml.DataXml.VALUE('(./PostTime[1])', 'DATETIME2') AS CREATE_DATETIME,
Txml.DataXml.VALUE('(./TSQLCommand[1]/CommandText[1])', 'NVARCHAR(max)') AS SQL_COMMANDE
FROM msdb.S_DDL.T_SUIVI_DDL AS E
CROSS APPLY E.DDL_DATA.nodes('/EVENT_INSTANCE') AS Txml (DataXml)
),
T1 AS
(
SELECT DATABASE_NAME, SCHEMA_NAME, OBJECT_NAME, OBJECT_TYPE,
CASE
WHEN EVENT_TYPE LIKE 'CREATE?_%' ESCAPE '?' THEN 1
WHEN EVENT_TYPE LIKE 'ALTER?_%' ESCAPE '?' THEN 2
WHEN EVENT_TYPE LIKE 'DROP?_%' ESCAPE '?' THEN 3
ELSE NULL
END AS ORDER_CODE, CREATE_DATETIME, SQL_COMMANDE,
DDL_ID, DDL_DH, DDL_APPLICATION, DDL_HOST, DDL_CONNEXION, DDL_USER
FROM T0)
SELECT DDL_ID, DATABASE_NAME, OBJECT_TYPE, SCHEMA_NAME, OBJECT_NAME, CREATE_DATETIME,
DENSE_RANK() OVER(ORDER BY DATABASE_NAME, OBJECT_TYPE, SCHEMA_NAME, OBJECT_NAME, CREATE_DATETIME, ORDER_CODE)
AS OBJECT_VERSION,
SQL_COMMANDE, DDL_DH, DDL_APPLICATION, DDL_HOST, DDL_CONNEXION, DDL_USER
FROM T1;
Certes la requête est un peu lourde, mais rien n’empêche d’en faire une vue…
Dans la colonne OBJECT_VERSION, vous avez maintenant le n° de version calculé…. pour tout objet de toute nature et avec le code de création correspondant !
CQFD
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
Bonjour,
article extrêmement intéressant et super pratique. Ça aurait pu me servir déjà avant (collègue qui travaille sur la même proc et qui raz une demi journée de travail…)
Merci !
A+
Remarque : dans la dernière requête, la casse de VALUE ne permettait pas d’exécuter la requête, j’ai dû la transformer en value (SQL Server 2008 R2).