Capture SQL Server Schema Changes Using the Default Trace

-- 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

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;