sql-server – 如何在Sql Server 2005上获取最后一次插入/更新/删除日期时间?

前端之家收集整理的这篇文章主要介绍了sql-server – 如何在Sql Server 2005上获取最后一次插入/更新/删除日期时间?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
不是 my previous question的副本

当表/数据库sql Server 2005上有插入/更新/删除时,有没有办法获得最新的日期时间?最好不要创建触发器..

我知道当你需要每行最后一次更新时,你需要触发器.但是我不确定当你想要获得整个表的最后一次更新时是否需要它们.

解决方法

您可以轻松获取上次插入/更新/删除的日期,如下所示:
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,INDID)) AS LASTUPDATED,CONVERT(VARCHAR,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')
原文链接:https://www.f2er.com/mssql/83736.html

猜你在找的MsSQL相关文章