October 17, 2006 at 2:43 am
Hi,
I need to track the changes that were done in the database schema in the last 24 hours (Like columns added to the tables, changes made to views, SPs etc) . How can I do it ?
Regards
Prabhu
October 17, 2006 at 4:45 pm
1. You can use DDL triggers.
2. If you are lucky and if you want to know who did it in past 24 hours, you can look into default SQL trace files and look for Schema change event.
October 18, 2006 at 1:23 am
If you are looking at doing this going forward then somthing like Red Gate's SQL Compare could help. You could use the toolkit API (or the command line version) to take a snapshot each day then run a comparison against the database and the snapshot (or even two snapshots) to see the differences over a 24 hour period.
This should save you writing any DDL triggers etc and also will generate any roll back/migration scripts you might need.
Hope this helps,
- James
--
James Moore
Red Gate Software Ltd
--
James Moore
Red Gate Software Ltd
October 18, 2006 at 1:50 pm
James brings up a good point. Although DDL triggers would generlly be sufficient enough, there are certain system procedures that do not fire DDL events. For example, sp_rename does not fire a DDL event when it is used to rename an object. SQL Compare would catch this change, whereas DDL triggers would not.
Or you could deny execute to this procedure to everyone except yourself. Off the top of my head, I don't know which other procedures do not fire DDL events.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply