Schema Changes History Report: See Who Dropped a Table

The Schema Changes History report shows who created, altered or dropped an object in a database. Management Studio builds it from the default trace, and the same data is available through T-SQL. Both ways follow, with the limits of each.

What the Report Shows

In Object Explorer, right-click the database and point to Reports. Choose Standard Reports, then Schema Changes History. The Schema Changes History report lists each object that was created, altered or dropped. Each row carries the time, the login name, the user name, the object name and the type of the object.

The report describes itself as a history of committed DDL statements recorded by the default trace. Expand an object, and you see every change that the trace holds for it. A dropped table shows up with its creation, its alterations and its drop.

Where the Data Lives

The default trace is a small trace that SQL Server runs on its own. It records schema changes and a few other events. It writes to a set of files, and it replaces the oldest file when the set is full. SQL Trace is deprecated, so build new long-term tracking on SQL Server Audit or Extended Events. The next query checks that the trace is on and shows its size limits.

SELECT CAST(value_in_use AS int) AS DefaultTraceEnabled
FROM sys.configurations
WHERE name = N'default trace enabled';

SELECT id, status, max_size AS MaxFileMB, max_files AS MaxFiles, is_rollover AS Rollover
FROM sys.traces
WHERE is_default = 1;

The trace is on, and it keeps five files of 20 MB each. On a busy server, those files can cover a short period. The report can show only what the files still hold. For a change from last month, the answer can already be gone.

Build a Demo

The first script creates a demo database. The second one creates a table, adds a column to it, and drops it. The three second wait gives the trace time to write its buffer to the file.

IF DB_ID(N'SchemaHistoryDemo') IS NULL CREATE DATABASE SchemaHistoryDemo;
GO
USE SchemaHistoryDemo;
GO
DROP TABLE IF EXISTS dbo.TestTable;

CREATE TABLE dbo.TestTable (ID int);
ALTER TABLE dbo.TestTable ADD FirstCol varchar(100);
DROP TABLE dbo.TestTable;
WAITFOR DELAY '00:00:03';

Query the Trace

The report doesn’t give you a query, but the trace itself is open to T-SQL. The function sys.fn_trace_gettable reads the trace files as a table. The query below joins it to the event names. It keeps three events: 46 for created, 47 for deleted and 164 for altered.

Two more filters need an explanation. The trace logs a begin and a commit for every change, so the query keeps subclass 1, the commit. A rolled back change gets subclass 2, and the filter hides it. That is right here, because a rolled back drop dropped nothing. The type 8277 means a user table. The last two lines keep only the changes of this session. Remove them to see every change on the server.

DECLARE @TracePath nvarchar(260) = (SELECT path FROM sys.traces WHERE is_default = 1);
SELECT t.StartTime,
       e.name AS Change,
       t.ObjectName,
       t.DatabaseName,
       t.LoginName,
       t.HostName,
       t.ApplicationName
FROM sys.fn_trace_gettable(@TracePath, DEFAULT) AS t
JOIN sys.trace_events AS e ON e.trace_event_id = t.EventClass
WHERE t.EventClass IN (46, 47, 164)
  AND t.EventSubClass = 1
  AND t.ObjectType = 8277
  AND t.DatabaseName = N'SchemaHistoryDemo'
  AND t.SPID = @@SPID
  AND t.StartTime >= (SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = @@SPID)
ORDER BY t.StartTime;

The table leaves out the LoginName and HostName columns. They hold the account and the machine of the person who ran the statement. Together they answer the question in the title. Your own login and machine name will show in those columns. The StartTime values come from one run. The ApplicationName column tells you the tool, which is Management Studio when someone used it.

Who Dropped a Database

The same trace records a dropped database, with the object type 16964. The name of the dropped database sits in the DatabaseName column, and ObjectName stays empty. The script creates a second database, drops it, and reads the trace.

USE master;
GO
CREATE DATABASE SchemaHistoryTwo;
GO
DROP DATABASE SchemaHistoryTwo;
GO
WAITFOR DELAY '00:00:03';
GO
DECLARE @TracePath nvarchar(260) = (SELECT path FROM sys.traces WHERE is_default = 1);
SELECT t.StartTime, e.name AS Change, t.DatabaseName, t.LoginName, t.HostName
FROM sys.fn_trace_gettable(@TracePath, DEFAULT) AS t
JOIN sys.trace_events AS e ON e.trace_event_id = t.EventClass
WHERE t.EventClass IN (46, 47)
  AND t.EventSubClass = 1
  AND t.ObjectType = 16964
  AND t.DatabaseName = N'SchemaHistoryTwo'
  AND t.SPID = @@SPID
  AND t.StartTime >= (SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = @@SPID)
ORDER BY t.StartTime;

Limits to Know

The login in the trace is the login that connected. When an application server connects with one shared login, every change shows that login. The HostName and ApplicationName columns then narrow the search. The time points to the job or the deployment.

The trace records schema changes, not data changes. A DELETE of rows never shows up here. To keep a longer history, copy the rows into a table on a schedule, before the files roll over. SQL Server Audit can also record schema changes for a longer time. A dropped database user or login is a security event, not a schema change. Use SQL Server Audit for it.

You could argue that the report is enough, because it needs no code. For a quick question about one table, that is true. The query wins when you need to filter by login, search every database or save the result.

What to Remember

Open the Schema Changes History report for one database, and use the query for everything else. Filter on the commit subclass to avoid duplicate rows. Read the LoginName, HostName and ApplicationName columns together. Copy the history out before the files roll over.

When you finish with the demo, run the cleanup script.

USE master;
GO
IF DB_ID(N'SchemaHistoryDemo') IS NOT NULL
BEGIN
    ALTER DATABASE SchemaHistoryDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE SchemaHistoryDemo;
END;
IF DB_ID(N'SchemaHistoryTwo') IS NOT NULL
BEGIN
    ALTER DATABASE SchemaHistoryTwo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE SchemaHistoryTwo;
END;

A dropped table is not a mystery, it is a row the default trace has not rolled over yet.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.