Capture SQL Server Schema Changes Using the Default Trace

By:   |   Comments (4)   |   Related: More > Database Administration


Throughout my career as a DBA as much as I'd like to have complete control of all of my databases and have only me (or my team) be the only ones with access to make changes, this is not always the case. There is inevitably someone with access or access to an account that has the ability to make changes to your database. This tip will look at a way to capture any changes that have been made to your database using the default trace.


Starting in SQL Server 2005 along with the default trace came a new report in SSMS called the "Schema Changes Report". If you are not already familiar with you can read more about here. This report contains some really useful information, but since the report uses the default trace it only shows data since the last restart of your SQL Server instance. In order to capture and save this information let's run the report in SSMS with SQL Profiler running and capture the query that is run in the background to provide the data for the report. After doing this I was able to capture this query which provides the schema changes for the database it was run against.

By looking at the first queries WHERE clause you can see the query limits the result set to "EventClass in (46,47,164)" which is any CREATE, DROP or ALTER statement. Should you want to include other events you can get a complete listing of the events by querying the sys.trace_events catalog view. Also notice in the WHERE clause for second query that actual returns the data, we exclude any "object_type not in (21587)" which is any auto-statistics update statements. A complete listing of these object types can be found here.

Before we can make any updates to this query to store the data we need to define a table that will hold the query output. The following is the DDL definition for this table. Note that I have created it in the msdb database, but if you have some other database within your instance where you store DBA related data you could put it there as well. Here is the T-SQL:

-- create table to hold DDL history
CREATE TABLE msdb.[dbo].[DDL_History](
[database_name] [nvarchar](256) NULL,
[start_time] [datetime] NULL,
[login_name] [nvarchar](256) NULL,
[user_name] [nvarchar](256) NULL,
[application_name] [nvarchar](256) NULL,
[ddl_operation] [nvarchar](40) NULL,
[object] [nvarchar](257) NOT NULL,
[type_desc] [nvarchar](60) NULL

Now that we have a place to store our data all we need to do is make a few updates to the query we captured earlier in order get the query result set saved into this table. We also have to get the query to run against all the databases in our SQL Server instance. The first update is an easy one and all we have to do is add an INSERT to the "SELECT ... FROM @temp_trace" statement. We'll also make some updates to the SELECT list of this query to match our table definition. For the second item we need to wrap this query in a cursor so we can loop through all the databases on our instance. You can see both of these updates in query code below.

USE [master]
--Declare variables
DECLARE @dbname VARCHAR(100);
DECLARE @execstr VARCHAR(2800);
-- Declare a cursor.
SELECT database_id,name from sys.databases where name not in ('master','tempdb');

-- Open the cursor.
OPEN dbs;
-- Loop through all the tables in the database.
FROM dbs
INTO @dbid,@dbname;

-- Check default trace for any DDL in specific database
SELECT @execstr='if (select convert(int,value_in_use) from sys.configurations where name = ''default trace enabled'') = 1 
declare @d1 datetime; 
declare @diff int; 
declare @curr_tracefilename varchar(500); 
declare @base_tracefilename varchar(500); 
declare @indx int ; 
declare @temp_trace table (
obj_name nvarchar(256)
, obj_id int
, database_name nvarchar(256)
, start_time datetime
, event_class int
, event_subclass int
, object_type int
, server_name nvarchar(256)
, login_name nvarchar(256)
, user_name nvarchar(256)
, application_name nvarchar(256)
, ddl_operation nvarchar(40) 

select @curr_tracefilename = path from sys.traces where is_default = 1 ; 
set @curr_tracefilename = reverse(@curr_tracefilename) 
select @indx = PATINDEX(''%\%'', @curr_tracefilename) 
set @curr_tracefilename = reverse(@curr_tracefilename) 
set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + ''\log.trc''; 

insert into @temp_trace 
select ObjectName
, ObjectID
, DatabaseName
, StartTime
, EventClass
, EventSubClass
, ObjectType
, ServerName
, LoginName
, NTUserName
, ApplicationName
, ''temp''
from ::fn_trace_gettable( @base_tracefilename, default ) 
where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID = '+cast(@dbid as varchar)+' ; 

update @temp_trace set ddl_operation = ''CREATE'' where event_class = 46;
update @temp_trace set ddl_operation = ''DROP'' where event_class = 47;
update @temp_trace set ddl_operation = ''ALTER'' where event_class = 164; 

select @d1 = min(start_time) from @temp_trace 
set @diff= datediff(hh,@d1,getdate())
set @diff=@diff/24; 

insert into msdb.dbo.DDL_History (database_name,start_time,login_name,user_name,application_name,ddl_operation,object,type_desc)
select database_name,start_time,login_name,user_name,application_name,ddl_operation,''.'' as "object",o.type_desc
from @temp_trace tt inner join
'+@dbname+'.sys.objects o on tt.obj_id=o.object_id inner join
'+@dbname+'.sys.schemas s on s.schema_id=o.schema_id
where object_type not in (21587) -- don''t bother with auto-statistics as it generates too much noise
and start_time > (select max(start_time) from msdb.dbo.DDL_History)

EXEC (@execstr)

FROM dbs
INTO @dbid,@dbname;
-- Close and deallocate the cursor.
CLOSE dbs;

Taking the TSQL code and dropping it into a SQL job we can now run this at whatever frequency we feel is appropriate for our environment and any changes that have been made in the databases in our instance will be saved to this new table we created. Below I've included a sample output just for reference so you can see first-hand the information that we've collected.









msdb 2015-08-27 14:08:40.460 sa sa Microsoft SQL Server Management Studio - Query CREATE dbo.DDL_History USER_TABLE
TestDB 2015-08-26 11:32:19.703 sa sa Microsoft SQL Server Management Studio ALTER dbo.SampleData USER_TABLE
TestDB 2015-08-26 11:32:19.743 sa sa Microsoft SQL Server Management Studio ALTER dbo.SampleData USER_TABLE
TestDB 2015-09-09 10:21:27.833 sa sa Microsoft SQL Server Management Studio ALTER dbo.SampleData USER_TABLE
TestDB 2015-09-09 10:21:27.833 sa sa Microsoft SQL Server Management Studio ALTER dbo.SampleData USER_TABLE

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, December 21, 2016 - 3:59:16 PM - Pete Miller Back To Top (45025)

Hello!!!  Great article, but I did find a few issues.


1) as listed in prior comments the where clause ( and start_time > (select max(start_time) from ServerManagement.dbo.DDL_History))   caused no records to be returned.  My fix below:

  • Commented the final condition to return all of the records.    Then created as the stored procedure and executed via the job.

2) The the capture of the DELETE did not work due to the INNER joins for the objects.    My fix below.

  • Modified final insert to use LEFT OUTTER joins
  • Modified select for the object name to: ISNULL(''.'', '''')+ COALESCE(, tt.obj_name, ''Unknown Object'') as "object",
    • This allows for seeding and drops as the and values would be NULL as they are from the right side of the join.

Hope this helps!  

Friday, August 26, 2016 - 11:00:49 AM - Karthik Back To Top (43194)

 Very very useful info. Thank you so much for posting!


Wednesday, October 14, 2015 - 11:20:40 AM - Gaby A. Back To Top (38891)

Love it.  I did notice a small bug though, at the end of the query, if the table is initially not seeded.  Hope this helps. :)


and start_time > 



when max(start_time) is NULL then ''1900-01-01''

else max(start_time)


from msdb.dbo.DDL_History)

Sunday, October 11, 2015 - 8:55:45 PM - Jeff Back To Top (38858)

This post is very good.  I perform database security and we have to monitor all changes to any of the database.  I will be sure to use this process to mitigate some of the findings.  

get free sql tips
agree to terms