-- create table to hold DDL history

CREATE TABLE sw_test.[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(4000);

-- 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 sw_test.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 case when max(start_time) is NULL then ''1900-01-01'' else max(start_time) end from sw_test.dbo.DDL_History)

end'

EXEC (@execstr)

FETCH NEXT

FROM dbs

INTO @dbid,@dbname;

END;

-- Close and deallocate the cursor.

CLOSE dbs;

DEALLOCATE dbs;