USE [Sparrow4db]
GO
/****** Object:  DdlTrigger [backup_objects]    Script Date: 09/03/2010 14:04:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE trigger [backup_objects]
on database
for create_procedure, alter_procedure, drop_procedure,
create_table, alter_table, drop_table,
create_function, alter_function, drop_function
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EventData XML
    SET @EventData = EVENTDATA();
 
    DECLARE @ip VARCHAR(32)
    SET  @ip=
        (
            SELECT client_net_address
                FROM sys.dm_exec_connections
                WHERE session_id = @@SPID
        );
 
INSERT master.dbo.DDLChangesTrack
    (
        EventType,
        EventDDL,
        DatabaseName,
        SchemaName,
        ObjectName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName
    )
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        DB_NAME(),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        HOST_NAME(),
        @ip,
        PROGRAM_NAME(),
        SUSER_SNAME();
END

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [backup_objects] ON DATABASE

create this trigger on database where you want to track and create "master.dbo.DDLChangesTrack" table in master DB for get tracking changes