How To Track 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.  

  USE AuditDB;
  GO
  
  CREATE TABLE dbo.ProcedureChanges
  (
      EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      EventType    NVARCHAR(100),
      EventDDL     NVARCHAR(MAX),
      DatabaseName NVARCHAR(255),
      SchemaName   NVARCHAR(255),
      ObjectName   NVARCHAR(255),
      HostName     NVARCHAR(255),
      IPAddress    VARCHAR(32),
      ProgramName  NVARCHAR(255),
      LoginName    NVARCHAR(255)
  );

• Add data of all existing stored procedures from your actual database (Product DB in this example)

  USE ProductDB;
  GO
  
  INSERT AuditDB.dbo.ProcedureChanges
  (
      EventType,
      EventDDL,
      DatabaseName,
      SchemaName,
      ObjectName
  )
  SELECT
      N'Initial control',
      OBJECT_DEFINITION([object_id]),
      DB_NAME(),
      OBJECT_SCHEMA_NAME([object_id]),
      OBJECT_NAME([object_id])
  FROM
      sys.procedures;

• Create DDL trigger to capture changes 

  USE ProductDB;
  GO
  
  CREATE TRIGGER CaptureStoredProcedureChanges
      ON DATABASE
      FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
      ALTER_SCHEMA, RENAME
  AS
  BEGIN
      SET NOCOUNT ON;
  
      DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32);
  
      SELECT @ip = client_net_address
          FROM sys.dm_exec_connections
          WHERE session_id = @@SPID;
  
      INSERT AuditDB.dbo.ProcedureChanges
      (
          EventType,
          EventDDL,
          SchemaName,
          ObjectName,
          DatabaseName,
          HostName,
          IPAddress,
          ProgramName,
          LoginName
      )
      SELECT
          @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),
          @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
          @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),
          @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
          DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME();
  END
  GO

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

 

 

The method might have some limitations, but this is the simplest way to tracking changes of small size databases.