I was thinking about writing a script to capture the objects that were either CREATED/DELETED or MODIFIED in the last 24hrs. Instead of directly querying the sys.objects I from where I can only get minimal information I decided to get a whole bunch of useful information using the SQL Server default trace.
By default the default trace will be running in all the SQL Servers since SQL 2005 onwards. It will capture minimal information without overloading the database. It will be placed in the same location as that of the SQL Server errorlog. The maximum file size is 20MB after which the trace will roll over into a new file. However, SQL Server can maintain only the 5 most recently created trace files. In addition a new trace file is generated whenever SQL Server is restarted.
Ive created the below stored procedure to accomplish what I wanted. In addition the procedure will give the info on who made the changes, what is the host name and when was the changes made and what was the hostname etc. Ofcourse, one might wonder why in the whole world one should read from the trace flag when there are various simple options like DDL trigger available. Also with the advent of SQL 2008 a DBAs job has been made relatively easy! ! with the excellent Auditing features. However, with triggers be it DDL or DML performance will certainly take a beating due to synchronous processing. As a result I decided to write a procedure to query the default trace to get all those information.
I am querying not only the current trace file but also the previous one so that If the Server was restarted in the last 24hrs and if someone made changes just prior to restarting then those changes will be available in the older trace files. So the below script will capture all those changes by reading both the trace files.