Capture SQL Server Schema Changes Using the Default Trace

By: Ben Snaidero   |   Updated: 2015-10-09   |   Comments (4)   |   Related: More > Database Administration

Problem

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.

Solution

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
) ON [PRIMARY]
GO

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]
GO
--Declare variables
SET NOCOUNT ON;
DECLARE @dbid INT;
DECLARE @dbname VARCHAR(100);
DECLARE @execstr VARCHAR(2800);
-- Declare a cursor.
DECLARE dbs CURSOR FOR
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.
FETCH NEXT
FROM dbs
INTO @dbid,@dbname;
WHILE @@FETCH_STATUS = 0
BEGIN;

-- 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 
begin 
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,s.name+''.''+o.name 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)
end'

EXEC (@execstr)

FETCH NEXT
FROM dbs
INTO @dbid,@dbname;
END;
-- Close and deallocate the cursor.
CLOSE dbs;
DEALLOCATE 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.

Next Steps

• Other tips on capturing DDL events:
  • SQL Server DDL trigger to track all database changes
  • Auditing DDL create alter drop commands in SQL Server 2005
• Other tips on using the default trace:
  • Using the SQL Server default trace to audit events
  • Default trace in SQL Server 2005

About the author

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