Skip to main content

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')