I know this is covered all over the place with various solutions and possibly somewhere in books online.
However, I found this code snippet in the AdventureWorks database which was disabled, I’ve changed it so that it didn’t disable at the end but other than that it’s all intact. DISCLAIMER – I DID NOT WRITE THIS, SOMEONE AT MS DID!
I’m sure to anyone who is going to use this the SQL is self-explanatory but very basically it creates a database trigger which inserts rows in to a table called dbo.DatabaseLog on every DDL command.
The script is called createddltrigger-sql.doc just drop the .DOC extension and replace with .SQL it’s a plain text file.