Google Answers Logo
View Question
 
Q: SQL Server - Need info on when a stored procedure was last updated ( No Answer,   3 Comments )
Question  
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
Answer  
There is no answer at this time.

Comments  
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.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

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 Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy