Solved

T-SQL identify last update

Posted on 2009-07-12
5
462 Views
Last Modified: 2012-05-07
How do I find the last datetime a database had a design update, i.e. a table, view or stored-procedure definition update.
(Needed for version control mechanism).
0
Comment
Question by:robleenheer
  • 2
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24836320
SQL Server doesn't keep track of the updated date, you can see the created_date in sysobjects table
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 total points
ID: 24836361
>>How do I find the last datetime a database had a design update<<
You do this yourself using version control software such as MS Source Safe or some home made app.
0
 

Author Comment

by:robleenheer
ID: 24836754
I did some digging, and this actually gives me the last definition update of tables, stored procedures and views:

     select max(modify_date) from sys.objects

The only thing is, if you delete(!) an object, the query sesult is that of the last object that was created or modified before the deleted object, which is great because it means I can muck around n my development DB and as long as I delete anything I created 'temporary', my version control module can still compare the 'modify_date' of the development DB agains the production DB.

This is exactly what I needed :)

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24842867
Just a couple of observations:

1. Your query will produce the last time any object was modified.  I suspect what you meant was:
Select modify_date
From sys.objects
Where name = "ObjectNameGoesHere"

2. The problem with this and the traditional:
select LAST_ALTERED
from information_schema.routines
Where ROUTINE_NAME = "ObjectNameGoesHere"

is that sp_Recompile will reset the dates, which is not very helpful as no code has actually changed.
0
 

Author Comment

by:robleenheer
ID: 24894463
Thanks for the additional info. I will have to check how that affects the behaviour of my version control module. But I suspect the worse that will happen is that application will assume teh associated DB to be 'updated' where in fact the definition is totally un-altered.

But thanks anyway. I will add more comments if anything significant (or any show-stoppers for what I'm trying to achieve) comes out of the wash.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now