SQL行插入日期 [英] SQL Row inserted Date

问题描述

我有一个数据的SQL表，它没有任何特定列的行更新日期或时间。现在我想知道插入行的时间。!!



提前使用。

I've a SQL table of Data which doesn't have any specific column for Row updated date or time. Now i want know when the row was inserted.!!

Thanx in Advance.

推荐答案

尝试使用此功能，

Try to use this function,

SELECT *,fn_TablesLastUpdateDate(0) as InsertedDate from yourtable

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

如果你想检查sql行的更新日期和时间，请使用如下查询：

if you want to check updated date and time of the sql row, use the query as below:

SELECT top 10 col.name 'ColumnName', MAX(STATS_DATE (ID,INDID)) 'DateTime'
    FROM    SYS.SYSINDEXES as Ind
INNER JOIN SYS.columns as Col ON Ind.ID = Col.OBJECT_ID
WHERE STATS_DATE (ID,INDID) IS NOT NULL and Col.name like '%ColummnName%'
GROUP BY Col.name

希望它有所帮助。

hope it helps.

这篇关于SQL行插入日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！