DBA Scripts: Get Schema Change History

“What Changed on This Server Recently?”

A query that worked yesterday now errors, a report comes back empty, a job that always succeeded fails, and everyone says nothing changed. Before chasing the symptom, answer the basic question directly: did anything change here?

This script reads the SQL Server default trace, on by default, and returns every CREATE, ALTER, and DROP still in it: what kind of object, who did it, from where, and when.

Why Schema Change History Matters

• “Nothing changed” is a claim people make confidently and are sometimes wrong about. This script checks the actual DDL history instead of relying on anyone’s memory
• The default trace captures changed_by, host_name, and application_name, enough to identify whether a change came from a person, a deployment pipeline, or an unexpected source, and object_type says whether the thing that changed was a table, an index, a procedure or a whole database
• The default trace keeps 5 rollover files of 20 MB, so its window is a volume, not a period: days on a quiet instance, hours on a busy one. This is a “recently” tool, not a permanent audit log
• It has 2 blind spots worth knowing before you rely on it: an sp_rename writes no DDL event at all, and a column added or dropped shows only as the table being altered, with no column name

When to Run This Script

• Immediately after noticing unexpected behavior with no obvious cause, before chasing the symptom further
• After a deployment, to confirm exactly what DDL actually ran, not just what the deployment was supposed to do
• Investigating who created, altered, or dropped a specific object
• Routine health checks, to build awareness of what’s actually changing on a server over time

The Script

/*
Script Name : Get-SchemaChangeHistory
Category    : monitoring
Purpose     : Recent DDL changes (CREATE, ALTER, DROP) captured by the SQL Server default trace - answers "what changed on this server recently?" after an incident or unexpected behaviour.
              Requires the default trace to be enabled (on by default). Reads every rollover file the trace still keeps, not only the current one.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-schema-change-history/)
Requires    : ALTER TRACE
Notes       : The default trace keeps 5 rollover files of 20 MB, so the window is a volume, not a period:
              days on a quiet instance, hours on a busy one. A service restart starts a new file but the
              older files are still read here.
              sp_rename writes no DDL event to the default trace, so a renamed object shows only its
              original Object:Created row under the old name.
              ALTER TABLE ADD or DROP COLUMN shows as Object:Altered on the table, with no column name.
              The default trace does not capture the statement text for these events, so there is no
              sql_text column: the who, where and when are all the trace holds.
              Database create and drop rows have no object name; object_type reads Database.
              Auto-created statistics (_WA_Sys_*) are excluded; a user CREATE STATISTICS is kept.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

DECLARE @tracepath NVARCHAR(260);
SELECT @tracepath = path FROM sys.traces WHERE is_default = 1;

IF @tracepath IS NULL
BEGIN
    RAISERROR('Default trace is not enabled. Enable it via sp_configure ''default trace enabled'', 1.', 16, 1);
    RETURN;
END;

/* sys.traces names the CURRENT file (log_NN.trc), and fn_trace_gettable reads from the file it is
   given forward, so passing that path skips every older rollover file. The base name log.trc in
   the same folder makes it read all the files that still exist. */
SET @tracepath = REVERSE(SUBSTRING(REVERSE(@tracepath), CHARINDEX(N'\', REVERSE(@tracepath)), 260)) + N'log.trc';

/* -- DDL event classes: 46 = Created, 47 = Deleted, 164 = Altered -------------------------------- */
SELECT
    t.StartTime AS change_time,
    te.name AS change_type,
    t.DatabaseName AS database_name,
    t.ObjectName AS object_name,
    CASE t.ObjectType
        WHEN 8277  THEN 'Table'
        WHEN 22601 THEN 'Index'
        WHEN 8272  THEN 'Stored procedure'
        WHEN 8278  THEN 'View'
        WHEN 20038 THEN 'Scalar function'
        WHEN 17993 THEN 'Inline table-valued function'
        WHEN 18004 THEN 'Table-valued function'
        WHEN 21076 THEN 'Trigger'
        WHEN 21572 THEN 'Database trigger'
        WHEN 8276  THEN 'Server trigger'
        WHEN 21587 THEN 'Statistics'
        WHEN 16964 THEN 'Database'
        WHEN 17235 THEN 'Schema'
        WHEN 20051 THEN 'Synonym'
        WHEN 22868 THEN 'Type'
        WHEN 20307 THEN 'Sequence'
        WHEN 8259  THEN 'Check constraint'
        WHEN 8260  THEN 'Default constraint'
        WHEN 8262  THEN 'Foreign key'
        WHEN 19280 THEN 'Primary key'
        WHEN 20821 THEN 'Unique constraint'
        WHEN 17747 THEN 'Event session' /* MS Docs lists 17747 as Security Event; CREATE/DROP EVENT SESSION writes it */
        ELSE 'Other (' + CAST(t.ObjectType AS VARCHAR(10)) + ')'
    END AS object_type,
    t.LoginName AS changed_by,
    t.HostName AS host_name,
    t.ApplicationName AS application_name
FROM sys.fn_trace_gettable(@tracepath, DEFAULT) t
JOIN sys.trace_events te ON te.trace_event_id = t.EventClass
WHERE t.EventClass IN (46, 47, 164) /* Object:Created, Object:Deleted, Object:Altered */
  AND t.EventSubClass = 1 /* Commit only - each DDL event is logged as Begin (0) then Commit (1), or
                             Rollback (2) when it never happened; without this every change doubles */
  AND ISNULL(t.DatabaseName, '') NOT IN ('', 'mssqlsystemresource')
  AND ISNULL(t.ObjectName, N'') NOT LIKE N'[_]WA[_]Sys[_]%' /* auto-created statistics are not schema
                             changes; ISNULL keeps database create and drop rows, which have no name */
ORDER BY t.StartTime DESC, t.EventSequence DESC;

The event class filter (46, 47, 164) is deliberately narrow, just the 3 DDL event types, so the output stays on schema changes rather than everything else the default trace records. It reads every trace file still on disk, not only the current one, so a service restart does not hide the changes made before it.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Recent CREATE, ALTER, and DROP activity from the default trace:
.\run.ps1 Get-SchemaChangeHistory

# To run against a remote sql server:
.\run.ps1 Get-SchemaChangeHistory -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/monitoring/error-log/Get-SchemaChangeHistory.sql

Example Output

One run against the lab instance, read for the rows of one database, Lab1004_sch_Sales: built by one login, then changed by a second one. 10 of its 12 rows are shown (the 2 primary key indexes created with their tables are left out), and 5 of the 8 columns, because database_name, host_name and application_name read the same on every row here.

Read it bottom up. The database, both tables, the index and the procedure were created by one login, then Lab1004_sch_deploy altered Orders twice, altered the procedure and dropped the index inside 1 second, which is what a deployment looks like in this output. The view on the top row came later, from the first login again. The 2 Orders rows are a column added and a column dropped, and nothing here says which column.

The table called Customers is now called Clients, and this output cannot tell you: sp_rename writes no DDL event to the default trace, so the only row that table has is its original create, under its old name.

The same script in SSMS on 14 October 2026, 1,824 changes back. The top six rows are a test database built a minute earlier, all by the login that ran the setup. Below them, NT SERVICE\SQLTELEMETRY creates, alters and deletes telemetry_xevents through SQLServerCEIP: that is SQL Server’s own telemetry service recycling its event session. It turns up on most instances, so it is one of the changed_by values to learn as normal.

Understanding the Results

Best Practices

• Reach for this first, before deeper investigation, whenever behavior changed with no obvious cause: it’s a fast, direct way to confirm or rule out a recent schema change
• Check the oldest change_time before you trust an absence: an empty result for your timeframe means the window has rolled past it as often as it means nothing changed
• Don’t rely on this as a permanent audit trail, the default trace’s window is limited; if you need guaranteed long-term DDL history, a dedicated audit specification is the right tool
• Cross-reference changed_by and application_name against your known deployment tooling: an unfamiliar combination on a production change is worth a closer look

MS Docs: sys.fn_trace_gettable, default trace enabled, and the ObjectType trace column that object_type is decoded from.

Frequently Asked Questions

Summary

“Did anything actually change here recently” is a question worth answering with real trace data, not memory. This script reads the default trace directly and returns every CREATE, ALTER, and DROP it captured, with who did it and from where, ready to either confirm or rule out a recent change as the cause of unexpected behavior.

Reach for it first when something’s behaving differently with no obvious explanation, and keep in mind the default trace’s window is limited, not a permanent record.

Related