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.
1. USE AuditDB;
2. GO
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
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
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;
12. DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32);
14. SELECT @ip = client_net_address
15. FROM sys.dm_exec_connections
16. WHERE session_id = @@SPID;
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.

Track Stored Procedure changes using DDL trigger

Create your audit database and create a table.

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

Create DDL trigger to capture changes

GO

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