Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

T-SQL identify last update

Posted on 2009-07-12
5
Medium Priority
?
473 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

722 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