How To Track Database Changes in SQL server

This article talks about tracking database changes in SQL Server

Version control helps you to track the changes of a code repository. But, it doesn't much help to track database changes. General practice is to create a single script file that includes all the schema and update that file every time you make any changes into the database and commit it to version control.
 
However, this is a bit longer a way to track the changes. Another way is to use popular tools like Red Gate Change Automation. But there is a native way around to handle tracking! simply put, DDL trigger can be used to track the DB changes.
 

Track Stored Procedure changes using DDL trigger 

 
Here we'll see how to track stored procedure changes using DDL trigger.
  • Create your audit database and create a table.  

    1. USE AuditDB;  
    2. GO  
    3.   
    4. CREATE TABLE dbo.ProcedureChanges  
    5. (  
    6.     EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  
    7.     EventType    NVARCHAR(100),  
    8.     EventDDL     NVARCHAR(MAX),  
    9.     DatabaseName NVARCHAR(255),  
    10.     SchemaName   NVARCHAR(255),  
    11.     ObjectName   NVARCHAR(255),  
    12.     HostName     NVARCHAR(255),  
    13.     IPAddress    VARCHAR(32),  
    14.     ProgramName  NVARCHAR(255),  
    15.     LoginName    NVARCHAR(255)  
    16. );   
  • Add data of all existing stored procedures from your actual database (Product DB in this example)

    1. USE ProductDB;  
    2. GO  
    3.   
    4. INSERT AuditDB.dbo.ProcedureChanges  
    5. (  
    6.     EventType,  
    7.     EventDDL,  
    8.     DatabaseName,  
    9.     SchemaName,  
    10.     ObjectName  
    11. )  
    12. SELECT  
    13.     N'Initial control',  
    14.     OBJECT_DEFINITION([object_id]),  
    15.     DB_NAME(),  
    16.     OBJECT_SCHEMA_NAME([object_id]),  
    17.     OBJECT_NAME([object_id])  
    18. FROM  
    19.     sys.procedures;  
  • Create DDL trigger to capture changes 

    1. USE ProductDB;  
    2. GO  
    3.   
    4. CREATE TRIGGER CaptureStoredProcedureChanges  
    5.     ON DATABASE  
    6.     FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,   
    7.     ALTER_SCHEMA, RENAME  
    8. AS  
    9. BEGIN  
    10.     SET NOCOUNT ON;  
    11.   
    12.     DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32);  
    13.   
    14.     SELECT @ip = client_net_address  
    15.         FROM sys.dm_exec_connections  
    16.         WHERE session_id = @@SPID;  
    17.   
    18.     INSERT AuditDB.dbo.ProcedureChanges  
    19.     (  
    20.         EventType,  
    21.         EventDDL,  
    22.         SchemaName,  
    23.         ObjectName,  
    24.         DatabaseName,  
    25.         HostName,  
    26.         IPAddress,  
    27.         ProgramName,  
    28.         LoginName  
    29.     )  
    30.     SELECT  
    31.         @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),   
    32.         @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]''NVARCHAR(MAX)'),  
    33.         @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),   
    34.         @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),  
    35.         DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME();  
    36. END  
    37. GO   

  • Modify any stored procedure and check the ProcedureChanges table from AuditDB. 

How to track Database changes in SQL server 
 
The method might have some limitations, but this is the simplest way to tracking changes of small size databases.