Guide
Four ways to see schema change history in SQL Server: default trace, DDL triggers, Extended Events and snapshots, and how much history each keeps.
A report breaks on Tuesday morning because a column it reads is gone. The first question is who dropped it, and the second, usually asked a little later, is what else changed at the same time. SQL Server can answer both, but only if the right mechanism was already in place before Tuesday, and each mechanism keeps a different amount of history.
Every SQL Server instance runs a lightweight default trace unless someone turned it off. It records object creation, alteration and deletion along with the login, host and application that did it. Management Studio exposes it as a standard report: right click the database, Reports, Standard Reports, Schema Changes History.
The same data can be queried directly:
DECLARE @path nvarchar(260) =
(SELECT REVERSE(SUBSTRING(REVERSE(path), CHARINDEX(N'\', REVERSE(path)), 260)) + N'log.trc'
FROM sys.traces WHERE is_default = 1);
SELECT t.StartTime, t.DatabaseName, t.ObjectName, e.name AS event_name,
t.LoginName, t.HostName, t.ApplicationName
FROM sys.fn_trace_gettable(@path, DEFAULT) AS t
JOIN sys.trace_events AS e ON e.trace_event_id = t.EventClass
WHERE e.name IN ('Object:Created', 'Object:Altered', 'Object:Deleted')
ORDER BY t.StartTime DESC;
The limitation is retention. The default trace keeps five rollover files of 20 MB each, shared with everything else it records, so on a busy instance the history covers days, sometimes hours. It answers "who dropped it this morning" well and "what changed last month" not at all. The dbatools command Get-DbaSchemaChangeHistory reads the same source, with the same horizon.
A database-level DDL trigger writes every schema change into a table you own, so the history lasts as long as you keep the rows:
CREATE TABLE dbo.schema_change_log (
id int IDENTITY PRIMARY KEY,
event_time datetime2 DEFAULT SYSUTCDATETIME(),
login_name sysname,
event_type nvarchar(100),
object_name nvarchar(256),
tsql nvarchar(max)
);
GO
CREATE TRIGGER trg_log_schema_changes ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON;
DECLARE @e xml = EVENTDATA();
INSERT INTO dbo.schema_change_log (login_name, event_type, object_name, tsql)
VALUES (
@e.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),
@e.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@e.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(128)') + '.' +
@e.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)'),
@e.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
);
END;
It captures the exact statement and the login, which is the richest record available. It also runs inside the transaction of every DDL statement, so a bug in the trigger or a full log table blocks deployments, and anyone with enough rights can disable it. It has to be installed on every database, including the ones created next year.
An Extended Events session on object_created, object_altered and object_deleted, writing to an event file, gives a similar record without running code inside the DDL transaction. Retention depends on the file size you configure, and reading the history means querying sys.fn_xe_file_target_read_file and shredding XML, which few people enjoy doing during an incident.
The three methods above record events. A different approach records state: read the catalog on a schedule, store the full description of every table, column, index, view, procedure and trigger, and compare each snapshot with the previous one.
What it gives up is the login. A snapshot knows that dbo.orders.discount_code disappeared between the scan at 06:00 and the scan at 12:00, not who ran the ALTER TABLE. A change that was made and reverted between two scans is invisible.
What it gains is a history that does not depend on anything running inside the server, works the same on Azure SQL Database where server-level traces and DDL triggers at server scope are not available, covers every object including those created after the setup, and can say whether a change is likely to break something: a dropped column or a narrowed type is more urgent than a new index.
Taavik's schema change detection is the snapshot approach. An agent inside the network reads the SQL Server catalog on a schedule, every 6, 12 or 24 hours or weekly, with daily as the most frequent option on the free plan, and the workspace compares each scan with the previous one. Renames are detected by matching the shape of the removed and added object, so a renamed table shows up as a rename rather than a drop plus a create.
Each change gets a severity. A removed column, a changed data type, a nullability flip or a dropped unique index is likely breaking; a modified view or procedure needs attention; an added object is informational. Alerts send the changes that match a rule to Slack, email or a webhook, and the generated documentation keeps one revision per change with a diff.
For teams that need the who as well as the what, the honest setup is both: a DDL trigger or the default trace for the login on the day it happens, and snapshots for the history that the trace does not keep.
The same comparison for other engines: tracking schema changes in PostgreSQL, MySQL schema change history without DDL triggers, auditing DDL in Azure SQL Database, and what Flyway and Liquibase drift checks miss.