Beginning SQL Server 2005 we now have the ability to create triggers at the database and server level to monitor for DDL events. Here is a DDL trigger to monitor for Create, Alter and Drop table events in your databases.
First Create a log table. ( I put mine in the msdb database for this example but you can either create your own log db or place it in your user db)
USE msdb GO CREATE TABLE ddl_log (ID int idenity(1,1) PRIMARY KEY CLUSTERED, PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));
Here is the DDL Trigger. The trigger will add a record to the log table and send an e-mail with the DDL statement. If you want to ROLLBACK the DDL you can do so by adding the ROLLBACK statement before the insert. Make sure you edit the email address.
CreateTRIGGER DDL_Notify ONDATABASE FOR DROP_TABLE, ALTER_TABLE,CREATE_TABLE AS DECLARE @data XML, @tableHTML NVARCHAR(MAX) ;