|
|
Subject:
SQL Server - Need info on when a stored procedure was last updated
Category: Computers > Software Asked by: datajockey-ga List Price: $10.00 |
Posted:
16 Aug 2006 19:00 PDT
Expires: 15 Sep 2006 19:00 PDT Question ID: 756831 |
I am looking to find out when a SQL Server stored procedure was last updated. A bonus if I can also find out who did it! I see that the sysobjects table has a create date & a ref date column, but I could not find a last update date. We are running the 'current' versions. I can dig up specific service pack numbers if required. We normally use Query Analyzer / ALTER procedure to make changes |
|
There is no answer at this time. |
|
Subject:
Re: SQL Server - Need info on when a stored procedure was last updated
From: tigher-ga on 17 Aug 2006 08:31 PDT |
Unforunately there is very little support for this in SQL server. The sysobjects table contains a column called schema_ver, this increases in value after every change however the date is not recorded. Unless you have already got a routine that runs daily to log these changes you can't really see when it was last updated. Do you have any daily backups, may be you can restore these systematically to a dev server until you find the change you are looking for, this would at least give you a date, unfortunately probably not a user. Best of luck. |
Subject:
Re: SQL Server - Need info on when a stored procedure was last updated
From: datajockey-ga on 17 Aug 2006 19:06 PDT |
Guess the answer is to avoid ALTER PROC and use DROP / CREATE instead. My problem with this approach is that I have to re-do all the permissions each time |
Subject:
Re: SQL Server - Need info on when a stored procedure was last updated
From: exs03-ga on 14 Sep 2006 11:03 PDT |
Here you go: USE [databasename] SELECT routine_name, last_altered FROM information_schema.routines WHERE routine_type = 'PROCEDURE' It does not capture who made the update but it definitely has the date and time. |
If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you. |
Search Google Answers for |
Google Home - Answers FAQ - Terms of Service - Privacy Policy |