robleenheer
asked on
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).
(Needed for version control mechanism).
Aneesh
SQL Server doesn't keep track of the updated date, you can see the created_date in sysobjects table
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 :)
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 :)
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.routine
Where ROUTINE_NAME = "ObjectNameGoesHere"
is that sp_Recompile will reset the dates, which is not very helpful as no code has actually changed.
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.routine
Where ROUTINE_NAME = "ObjectNameGoesHere"
is that sp_Recompile will reset the dates, which is not very helpful as no code has actually changed.
ASKER
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.
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.