sys.objects 中的 CreateDate 和 ModifyDate 到底是什么 [英] What exactly are CreateDate and ModifyDate in sys.objects

问题描述

我继承了以下管理查询，并在完全理解它返回的内容的情况下不时运行它:

I've inherited the following admin query and run it from time to time with fully understanding what is it returning:

--Loop until the Cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    --Dump the results of the sp_spaceused query to the temp table
    INSERT  #TempTable
        EXEC sp_spaceused @TableName

    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName
END

--get rid of the Cursor
CLOSE tableCursor
DEALLOCATE tableCursor



--Select TABLE properties with SIZE -- Final step
SELECT name, 
convert(date,create_date) as CreateDate, 
convert(date,modify_date) as ModifyDate, 
numberofRows, 
dataSize

FROM sys.objects
join #temptable tm on
tm.tablename = sys.objects.name
WHERE type  in ('U')

order by modify_date 
GO

以下字段是什么?:

1. "create_date" ...我猜是什么时候运行"CREATE TABLE ..."
2. "modify_date" ...这是上次更改表架构的时间吗?

他们中的任何一个告诉我上次数据是DELETED 还是INSERTED 到表中的时间吗?
如果没有，那么我如何获得这些信息?

Does either of them tell me the last time data was DELETED or INSERTED into the tables?
If not then how do I get this information?

推荐答案

what BOL 说

修改日期 - 上次使用 ALTER 修改对象的日期陈述.如果对象是表或视图，则 modify_date 也创建表或视图上的聚集索引时发生变化或改变了.

modify date - Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or altered.

所以这是有人添加列或更改表架构的时刻

so it is moment when someone added column or changed schema of the table

默认情况下不存储信息(当有人插入/删除值时)

by default that information (when someone inserted/deleted value) is not stored

如果你想要有人在表中插入值的时间，你必须实现它您可以将 ChangeDate datetime 列添加到您的表中并添加触发器，以便插入适当的值，但在删除数据时不会保留

if you want the time when someone inserted a value into the table you have to implement it you can add ChangeDate datetime column into your table and add trigger wich would insert appopriate value, but it would not keep when data was deleted

典型地，如果您想记录数据更改，您可以通过创建与您想记录的表类似的表来实现它，添加诸如DataChange, operation, user"之类的列并为 UPDATE、INSERT、DELETE 实现 DML 触发器

typical if you want to log data changes you can implement it by creating similar table to the one you want to log , add columns like 'DataChange, operation, user' and implement DML Trigger for UPDATE, INSERT, DELETE

或使用 sql server 更改数据来跟踪数据更改，但我个人从未使用过那个 :)

or use sql server change data for tracking data changes , but i personally never used that one : )

这篇关于sys.objects 中的 CreateDate 和 ModifyDate 到底是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！