Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

"Nothing has changed" - Determining when a procedure has been altered

As a consultant, I regularly need to determine when a stored procedure was last altered. Without having implemented a series of DDL triggers, how can this be accomplished?

In Microsoft SQL Server, you can easily retrieve this information from the sys.procedures catalog view. The following query demonstrates this.

SELECT 
[name]
,modify_date
,create_date
,*
FROM
sys.procedures

 

Of course you can take it a step further by limiting the results to a period of time where you know that no changes should have been made. For example, the following query lists all stored procedures that have been changed since August 1, 2007 (the time I last visited this client).

SELECT 
[name]
,modify_date
,create_date
,*
FROM
sys.procedures
WHERE
modify_date > '2007-08-01'


Although, using this technique will not allow me to identify who made the change, I can at least determine that a change has taken place.

Cheers!

Joe


kick it on DotNetKicks.com

Print | posted on Wednesday, August 29, 2007 1:30 AM | Filed Under [ T-SQL SQL Server ]

Feedback

Gravatar

# re: "Nothing has changed" - Determining when a procedure has been altered

Handy, thanks.
8/30/2007 8:23 AM | Tom
Gravatar

# re: "Nothing has changed" - Determining when a procedure has been altered

Nice tip! Thanks.
9/8/2007 3:28 PM | Chris Pietschmann
Gravatar

# re: "Nothing has changed" - Determining when a procedure has been altered

Note this only works on SQL 2005 - this wont work in sql 2000 or before. The modify date was never updated in earlier versions which meant you couldnt use get the last modified date.

Works great in sql 2005 thanks for that - I use Red gates sql compare tool which simplifies everything you could ever want for database releases and such like.
9/10/2007 2:14 AM | Gregor Suttie
Gravatar

# re: "Nothing has changed" - Determining when a procedure has been altered

If you want to weed-out the stored procedures that come with SQL Server, use this:

SELECT
[name]
, modify_date
, create_date
FROM
sys.procedures
WHERE
is_ms_shipped = 0
4/4/2008 9:42 AM | Patrick Spence
Gravatar

# re: "Nothing has changed" - Determining when a procedure has been altered

Thanks a ton
7/3/2008 12:46 AM | Krishna
Gravatar

# re: "Nothing has changed" - Determining when a procedure has been altered

Of course you can take it a step further by limiting the results to a period of time where you know that no changes should have been made. For example, the following query lists all stored procedures that have been changed since August 1, 2007 (the time I last visited this client).

snow boots | columbia jackets | mac makeup | the north face outlet
10/20/2010 2:19 AM | snow boots for women
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET