Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Capture SQL Server Schema Changes Using the Default Trace


By:   |   Last Updated: 2015-10-09   |   Comments (4)   |   Related Tips: 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 @[email protected]/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
'[email protected]+'.sys.objects o on tt.obj_id=o.object_id inner join
'[email protected]+'.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.

database
name

start
time

login
name

user
name

application
name

ddl
operation

object

type
desc

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


Last Updated: 2015-10-09


next webcast button


next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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(s.name+''.'', '''')+ COALESCE(o.name, tt.obj_name, ''Unknown Object'') as "object",
    • This allows for seeding and drops as the o.name and s.name 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

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

 


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

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 > 

(select 

case

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

else max(start_time)

end

from msdb.dbo.DDL_History)


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

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.  


Learn more about SQL Server tools