Tracking Schema Changes using DDL Triggers

The simplest way to check the schema changes history of a database is to check the Schema Changes History report , which can be opened in SSMS through right-click you SQL Server > Reports > Standard Reports > Schema Changes History. This built-in report shows the changes you’ve made using Data Definition Language (DDL) statements, relying on the Default Trace mechanism. (SQL Server records certain events about your system in a "Default Trace", which is a binary file that watches SQL Server for certain events. It uses the SQL Trace mechanism indeed). But the built-in report has some weaknesses. It "rolls over", so the changes history isn't persisted forever. It doesn't show the DDL statements ran. Also it just shows the impersonated login (using EXECUTE AS statement) rather than the original login.

You can track the schema change better by implementing DDL Triggers yourself. Below is the script.

CREATE DATABASE AuditDB;
GO
USE AuditDB;
GO
CREATE TABLE DDLEvent (
    eventDate datetime NOT NULL DEFAULT GETDATE(),
    eventType nvarchar(255),
    eventDDL nvarchar(max),
    eventXML xml,
    databaseName nvarchar(255),
    schemaName nvarchar(255),
    objectName nvarchar(255),
    hostName nvarchar(255),
    ipAddress nvarchar(255),
    programName nvarchar(255),
    loginName nvarchar(255)
);
GO
CREATE CLUSTERED INDEX CX_DDLEvent ON DDLEvent (eventDate)
GO
-- Modified it to your DB name
USE YourDB;
GO
CREATE OR ALTER TRIGGER DDLTrigger_AuditSchemaChange
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER, CREATE_TABLE, ALTER_TABLE, DROP_TABLE, RENAME
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @eventData xml = EVENTDATA();
    DECLARE @ip nvarchar(255) = CONVERT(nvarchar(255), CONNECTIONPROPERTY('client_net_address'));

    INSERT AuditDB.dbo.DDLEvent (
        eventType, eventDDL, eventXML, databaseName, schemaName, objectName, hostName, ipAddress, programName, loginName
    ) SELECT
        @eventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(255)'),
        @eventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
        @eventData,
        DB_NAME(),
        @eventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(255)'),
        @eventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(255)'),
        HOST_NAME(),
        @ip,
        PROGRAM_NAME(),
        ORIGINAL_LOGIN()
    ;
END
GO

By default, once you create a DDL trigger, it will be enabled immediately. Then you can check the audit table using SELECT statement, as illustrated below.