March 21, 2007 at 9:37 am
Hello,
Does anyone have a script to log creation of database objects. Is it possible to write a script to log user, object name, date, database, object type.
Is there any other valuable information that I should log as well.
If someone already has this, can you share please.
Thanks.
March 21, 2007 at 1:51 pm
Check out DDL triggers in BOL and you should be able to get what you need.
March 21, 2007 at 1:59 pm
DDL Triggers are SQL Server 2005 only.
In SQL Server 2000 the best solution I've come up with is to log a list of object names and versions from sysobjects and put that in a table. Then run it the next day and do a comparison to see what's changed. A new object will show up in an outer join. Changed objects show up as a different version number. This gets changes made by ALTER
If you just want new objects, query sysobjects and use crdate as the field for the date of creation.
March 23, 2007 at 9:13 am
Steve,
I do something very similar but find that index rebuilds done from maintenance plans change the version numbers. Do you know if there is any way round this (other than stop using maintenance plans to do index rebuilds)?
March 23, 2007 at 12:25 pm
No, no other way around it. The thing you can do is run a report that compares version numbers. They increment by 8 if I remember, so a change of 8 the day after the log rebuild isnt' a problem. A change of 16 would be.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply