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