Here you go mate here is one i created to do exactly what you ask, dont forget to create the log table its in the comments, this SP will log when and a stored proc, tirgger, Index is created, altered or deleted and when a table is altered only. It logs Date, who, etc in the comments there is also a section to deny updates or deletes to the log table :w00t:

Create trigger SCHEMA_AUDIT on database

for CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,

CREATE_INDEX , ALTER_INDEX ,DROP_INDEX,

CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,

ALTER_TABLE

as

begin

set nocount on

/*

Ref:

Name: DDL_TRIGGERS.sql

Version: 1.0

Author: Glen Wass

Date Created:22/05/2012

Description: create audit table and audit trigger to track stored procedure trigger index creation and changes

Impact:

Warnings:

Version History: DateVersionAuthorComments

---------------------------------------------------------------------------

22/05/2012|1.0| GW | Initial release

06/08/2012|1.1| GW| addded alter table

create table VEH_SCHEMA_LOG (id int identity,

USER_NAME nvarchar(255),

EVT_TYPE nvarchar (255),

OBJ_NAME nvarchar(255),

OBJ_TYPE nvarchar(255),

CREATE_DTTM datetime)

--- to stop users from altering the log table

DENY DELETE, UPDATE, ALTER on VEH_SCHEMA_LOG to [domain\DeptSqlUsers],

*/

Declare @data xml

set @data=Eventdata()

Insert into VEH_SCHEMA_LOG values

(

@data.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(255)'),

@data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(255)'),

@data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(255)'),

@data.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(255)'),

getdate()

)

set nocount off

end