views:

153

answers:

2

Is there any way to get a table's last modification datetime on Sql Server 2005? Preferably without creating triggers..

And for databases?

EDIT The solution of this question contemplated only CREATE and ALTER modifications. For INSERT, UPDATE and DELETE please look my next question

+1  A: 

this will list all object types from most recent change to oldest, you can easily modify as necessary...

DECLARE @SearchParams   varchar(500)
SET @SearchParams='yourObjectName'

SELECT
    CONVERT(varchar(23),modify_date,121) AS modify_date
        ,type_desc
        ,name
    FROM sys.objects
    WHERE is_ms_shipped=0 AND name LIKE '%'+@SearchParams+'%'
    ORDER BY modify_date DESC

if you just want tables, try:

DECLARE @SearchParams   varchar(500)
SET @SearchParams='YourTableName'
SELECT
    CONVERT(varchar(23),modify_date,121) AS modify_date
        ,type_desc
        ,name
    FROM sys.objects
    WHERE type='U'
        AND name LIKE '%'+@SearchParams+'%' --can use "=@SearchParams" if you have the entire table name
    ORDER BY modify_date DESC
KM
While it works, it wasn't what I expected. I'll rephrase the question.
Jader Dias
rephrased and linked the rephrased question in the question body
Jader Dias
+1  A: 

KM's solution will show when a table object (the table definition) was last modified. There is no built-in way to determine when the data within a give row was last updated (or inserted or, of course, deleted). The only 99% reliable way I've ever found to do that is with triggers, with that missing 1% representing when some troublemaker disables the trigger.

Philip Kelley
from the OP's question, I was thinking "table modification"=table structure changes, thus my answer. if "table modification"=insert/update/delete then @Philip Kelley is correct, you need a trigger, just watch out if you update a last changed row in antother table to track the changes, you may create blocking there.
KM
Great comment KM.
Jader Dias