Determine date and time a table was last updated

[WIREMESH]

Using VFP 9.0 and SQL Server 2000/2005 I need to determine the last time a table was modified (insert/update/delete)in SQL server. How can I accomplish this using sql passthru?

 

[Mike Gagnon]

Do you have a field in the SQL table that puts a datetime stamps in it?
Otherwise you would need to use an API call (or WMI) to determine the last time the file was changed.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[Mike Lewis]

Wiremesh,

The nearest I can think of is to send this command to the server:

select @@DBTS

That will give you the latest timestamp, but for the database as a whole, not for a given table.

Also, the timestamp is just a 64-bit number. I don't know if it's possible to convert that to the actual time of day.

Perhaps you need to maintain a "time of last update" column in the table, and to ensure that all your inserts and updates automatically post the system date to it. One way to do that would be to set its DEFAULT constraint to GETDATE(). Then, whenever you do an UPDATE, include the keyword DEFAULT for that column.

Sorry I can't suggest anything better. You might try asking over in one of the SQL Server forums.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Mike Lewis]

Wiremesh,

Another thought ....

Write a trigger on UPDATE, INSERT and DELETE that stores GETDATE() in another table. Then do a SELECT on that other table.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk