Solved

T-SQL identify last update

Posted on 2009-07-12
5
461 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 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

11 Experts available now in Live!

Get 1:1 Help Now