August 1, 2006 at 6:54 am
Using Sql Server 2005. Does anyone know how to determine the date and time when a sp was last changed and who did it? I can query sysobjects to get the create date but cannot determine when the sp was changed or the userid of the person who changed it.
August 1, 2006 at 7:02 am
You cannot find that information in sysobjects table. But if you need to keep track of the objects that are changed you can set a DDL trigger. When any objects changed your customised DDL fires and can store information you need. Look BOL under DDL triggers.
Cheers,
Zubeyir
August 1, 2006 at 12:52 pm
Hi,
When your DB is selected in SSMS click on Report in the Summary Window and look for the 'Schema Changes History' report. You can profile if you would like to use the query yourself.
Kr,
Wesley
August 1, 2006 at 2:49 pm
Thanks Wesley, I didn't know that
But that info just displays the general statement (create, alter for instance) but it does not display what really happened to a table or a procedure. If all we need is to monitor which objects are chanced than this report is great but if we need to track what changes are made we need some more. Do you know that kind of solution SQL 2K5 provides.
Cheers,
Zubeyir
August 2, 2006 at 6:34 am
Thanks Wesley and Zubeyir. I never even noticed the report button on the summary screen. There is a wealth of information there. That will help me trace sp changes as far as when and who. My question now is what do you mean by profile to use the query myself? How can I get the query that is being run?
August 4, 2006 at 2:46 pm
Not out of the box I'm afraid.
I suppose this is indeed where DDL Trigger come in.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply