Solved

T-SQL identify last update

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 - Merge Replication Issue 1 22
How to search for strings inside db views 4 27
TSQL query to generate xml 4 34
Tsql query 6 22
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

773 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