DBA Scripts: Get Schema Change History

---

“What Changed on This Server Recently?”

That question comes up after almost every unexpected behavior change: a query that used to work now errors, a report that used to return data now doesn’t, a job that used to succeed now fails. Before chasing the symptom, it’s worth answering the more basic question directly: did anything actually change here?

This script reads the SQL Server default trace, on by default, and returns every CREATE, ALTER, and DROP it captured, 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
• The default trace has a rolling window, it doesn’t keep DDL history forever, so this is a “recently” tool, not a permanent audit log
• Expect to see auto-generated objects (statistics, in particular) show up alongside genuine schema changes, distinguishing the two is part of reading this output correctly

---

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). Covers the rolling window kept by the trace files.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-schema-change-history/)
Requires    : VIEW SERVER STATE, ALTER TRACE (to read default trace path)
*/
-- Blog: https://sqldba.blog/dba-scripts-get-schema-change-history/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT 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;

/* ── 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,
    t.LoginName AS changed_by,
    t.HostName AS host_name,
    t.ApplicationName AS application_name,
    LEFT(CAST(t.TextData AS NVARCHAR(MAX)), 500) AS sql_text
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 ISNULL(t.DatabaseName, '') NOT IN ('', 'mssqlsystemresource')
ORDER BY t.StartTime DESC;

The event class filter (46, 47, 164) is deliberately narrow, just the three DDL event types, so the output stays focused on schema changes rather than the full breadth of everything the default trace captures.

---

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

Real output from this lab instance, not staged (578 rows in the current trace window, condensed):

change_time	change_type	database_name	object_name	changed_by	application_name
2026-07-27 16:40:26	Object:Created	DemoDatabase	NolockDemo	PWSQL01\Peter	Core Microsoft SqlClient Data Provider
2026-07-27 16:41:29	Object:Deleted	DemoDatabase	NolockDemo	PWSQL01\Peter	Core Microsoft SqlClient Data Provider
2026-07-27 17:25:05	Object:Created	SalesDemo	_WA_Sys_0000000E_0BC6C43E	PWSQL01\Peter	Core Microsoft SqlClient Data Provider

The NolockDemo rows are a genuine table create and drop from testing on this lab instance. The _WA_Sys_... rows are auto-generated statistics objects, created automatically by the query optimizer whenever it needs a statistic that doesn’t exist yet, they show up as Object:Created DDL events even though nobody explicitly created anything. On a busy, actively-queried lab database, these auto-stats entries are often the majority of what this script returns.

---

Understanding the Results

• _WA_Sys_... object names — auto-created statistics, not a real schema change; expected noise, not something to investigate
• application_name identifying a deployment tool or pipeline — confirms a change came from an automated process rather than a manual session, useful for confirming a deployment did what it claimed to
• changed_by showing an unexpected account — worth investigating directly, especially for a change nobody on the team remembers making
• No rows at all for the timeframe you expected — the default trace’s rolling window may have already aged out the change you’re looking for; the trace doesn’t keep history forever

---

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
• Filter out auto-generated statistics mentally (or with an additional WHERE object_name NOT LIKE '_WA_Sys%' if the noise is getting in the way) rather than treating every row as a real change
• 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

---

Related Scripts

You may also find these scripts useful:

• Security (hub)
• Audit Specifications, DDL Triggers, and Proxy Credentials
• DBA Scripts: The Complete Guide, the map across every script on this site

---

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.