How to track changes in table structure in Sql Server

SQL Server DDL triggers can be used in order to track DDL changes, whether the changes were intentional or accidental. Who committed the change, when, and what was affected by the committed action is the basic information needed to be captured using triggers. To use this solution, it’s required to create and maintain both triggers and captured information storage

In the following example, information about the event that fired the trigger is captured using the SQL Server’s EVENTDATA() function. The SQL script creates DDL trigger that captures CREATE, ALTER, and DROP events on a database level (although, triggers can be created on the server level to capture events for all databases on the server; ON ALL SERVER option should be used, instead of ON DATABASE):

CREATE TRIGGER Audit_DDLON DATABASE    FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLEAS     DECLARE        @event xml;     SET     @event = EVENTDATA();     INSERT INTO Audit_DDL_Events     VALUES     (     REPLACE(CONVERT(varchar(50),     @event.query('data(/EVENT_INSTANCE/PostTime)')), 'T', ' ')     ,     CONVERT(varchar(150),     @event.query('data(/EVENT_INSTANCE/LoginName)'))     ,     CONVERT(varchar(150),     @event.query('data(/EVENT_INSTANCE/UserName)'))     ,     CONVERT(varchar(150),     @event.query('data(/EVENT_INSTANCE/DatabaseName)'))     ,     CONVERT(varchar(150),     @event.query('data(/EVENT_INSTANCE/SchemaName)'))     ,     CONVERT(varchar(150),     @event.query('data(/EVENT_INSTANCE/ObjectName)'))     ,     CONVERT(varchar(150),     @event.query('data(/EVENT_INSTANCE/ObjectType)'))     , CONVERT(varchar(max), @event.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) );

An appropriate storage table for the auditing data from EVENTDATA XML must be created also:

CREATE TABLE Audit_DDL_Events(             DDL_Event_Time            datetime             ,             DDL_Login_Name            varchar(150)             ,             DDL_User_Name             varchar(150)             ,             DDL_Database_Name         varchar(150)             ,             DDL_Schema_Name           varchar(150)             ,             DDL_Object_Name           varchar(150)             ,             DDL_Object_Type           varchar(150)             ,             DDL_Command              varchar(max));

You can use DDL triggers to track changer to a database structure. Nice article on that subject can be found here.

3rd party edit

To quote from the linked article

DDL triggers ... fire stored procedures in response to an event. They fire in response to a variety of Data Definition Language (DDL) events. These events are specified by the T-SQL statements that are start with the keywords CREATE, ALTER, and DROP. ... These are used for administrative tasks like auditing and regulating database operations.