Sep 20, 2014

Keep the tables, stored procedures schema definition changes revision history in sql server

Sql server management studio (SSMS) doesn't keep or tack schema changes or modification or revision script for the database objects like tables, stored procedure, function, indexes view etc. like SVN.  

We can achieve this goal by using DDL trigger. 
Step 1: Create a table in your master database:

USE master
CREATE TABLE ObjectHistory(Data XML)

Step 2: Create a database view in your master database:

USE master

CREATE VIEW ObjectChangeHistory
AS
      SELECT
            REPLACE(C.value('./EventType[1]','sysname'),'_',' ') AS EventType,
            C.value('./ObjectType[1]','sysname') AS ObjectType,
            C.value('./PostTime[1]','datetime') AS EventTime,
            C.value('./SPID[1]','tinyint') AS SessionID,
            C.value('./LoginName[1]','sysname') AS LoginName,
            C.value('./UserName[1]','sysname') AS UserName,
            C.value('./ServerName[1]','sysname') AS ServerName,
            C.value('./DatabaseName[1]','sysname') AS DatabaseName,
            C.value('./SchemaName[1]','sysname') AS SchemaName,
            C.value('./ObjectName[1]','sysname') AS ObjectName,
            RTRIM(LTRIM(C.value('./TSQLCommand[1]/CommandText[1]','varchar(max)'))) AS ObjectDefintion
      FROM ObjectHistory CROSS APPLY Data.nodes('/EVENT_INSTANCE') AS T(C)

Step 3: Create below DDL trigger in your master database:

USE master

CREATE TRIGGER TRgObjectHistory
ON ALL SERVER
FOR CREATE_TABLE,
      ALTER_TABLE,
      CREATE_INDEX,
      ALTER_INDEX,
      CREATE_VIEW,
      ALTER_VIEW,
      CREATE_PROCEDURE,
      ALTER_PROCEDURE,
      CREATE_FUNCTION,
      ALTER_FUNCTION,
      CREATE_TRIGGER,
      ALTER_TRIGGER
AS
INSERT INTO ObjectHistory VALUES(EVENTDATA())

You have done!! To fetch the object modification script, execute following query:


SELECT * FROM dbo.ObjectChangeHistory