How to get last insert/update/delete datetime on Sql Server 2005?

Question

not a duplicate of my previous question

Is there any way to get the latest datetime when a table/database had an insert/update/delete on Sql Server 2005? Preferably without creating triggers..

I know that when you need the last update per row, you need triggers. But I am not sure if they are needed when you just want to get the last update for the whole table.

Answer 1

SELECT t.name,
       user_seeks,
       user_scans,
       user_lookups,
       user_updates,
       last_user_seek,
       last_user_scan,
       last_user_lookup,
       last_user_update
FROM   sys.dm_db_index_usage_stats i
       JOIN sys.tables t
         ON ( t.object_id = i.object_id )
WHERE  database_id = DB_ID() 

Answer 2

You Can Easily Get the Last Inserted/Updated/Deleted Dates as Follows:

CREATE FUNCTIOn fn_TablesLastUpdateDate(@Date NVARCHAR(20))

RETURNS @table TABLE(TableName NVARCHAR(40), LastUpdated Datetime)

AS

BEGIN


IF(@Date='') OR (@Date Is Null) OR (@Date='0')

    BEGIN
        INSERT INTO @table
        SELECT TOP 100 PERCENT TABLENAME,LASTUPDATED FROM 
        (
            SELECT  B.NAME AS 'TABLENAME', MAX(STATS_DATE (ID,INDID)) AS LASTUPDATED
            FROM    SYS.SYSINDEXES AS A
                    INNER JOIN SYS.OBJECTS AS B ON A.ID = B.OBJECT_ID
            WHERE   B.TYPE = 'U'  AND STATS_DATE (ID,INDID) IS NOT NULL 
            GROUP BY B.NAME
        ) AS A
        ORDER BY LASTUPDATED DESC
    END
ELSE

    BEGIN
        INSERT INTO @table
        SELECT TOP 100 PERCENT TABLENAME,LASTUPDATED FROM 
        (
            SELECT  B.NAME AS 'TABLENAME', MAX(STATS_DATE (ID,INDID)) AS LASTUPDATED,
                    CONVERT(VARCHAR, MAX(STATS_DATE (ID,INDID)), 103) as Date
            FROM    SYS.SYSINDEXES AS A
                    INNER JOIN SYS.OBJECTS AS B ON A.ID = B.OBJECT_ID
            WHERE   B.TYPE = 'U'  AND STATS_DATE (ID,INDID) IS NOT NULL 
            GROUP BY B.NAME
        ) AS A
        WHERE Date=@Date
        ORDER BY LASTUPDATED DESC
    END
RETURN

END



-- SELECT * from fn_TablesLastUpdateDate('06/11/2012')