T-SQL identify last update

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).
robleenheerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
SQL Server doesn't keep track of the updated date, you can see the created_date in sysobjects table
0
Anthony PerkinsCommented:
>>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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
robleenheerAuthor Commented:
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
Anthony PerkinsCommented:
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
robleenheerAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.