如何在SQL Server 2005中返回表的上次修改日期？

How can a Table's Last Modified date be returned in SQL Server 2005?

如何在SQL Server 2005中返回表的上次修改日期?

I did see one on the Table Properties page. There is a Created Date but no Modified date.

我确实在Table Properties页面上看到了一个。有创建日期但没有修改日期。

If it is not available, what would be some other ways to add this functionality?

如果它不可用,那么添加此功能的其他方法是什么?

Here are a few that come to mind:

以下是一些想到的:

1. Add another column, to the table, that a trigger would update whenever the record was added or changed. The one con, to this approach, is Deletes would not be tracked.

向表中添加另一列,只要添加或更改记录,触发器就会更新。对于这种方法,一个骗局是不会跟踪删除。

2. Add another table (TableModifiedDate) that would contain a Table Name and Modified Date. Then add a trigger to the tables that you want to track which will update TableModifiedDate.

添加另一个包含表名和修改日期的表(TableModifiedDate)。然后向要跟踪的表添加触发器,以更新TableModifiedDate。

2 个解决方案

#1

This Blog entry contains information on how to do it on SQL Server 2008 and 2005.

此博客条目包含有关如何在SQL Server 2008和2005上执行此操作的信息。

• On 2008: using the new Server Auditing feature

2008年:使用新的服务器审核功能

• On 2005: using Dynamic Management Views (DMV)

2005年:使用动态管理视图(DMV)

SQL Server 2000 has no built-in possibility to do it, so you'll need a workaround, like you already mentioned.

SQL Server 2000没有内置的可能性,因此您需要一个解决方法,就像您已经提到的那样。

#2

Paul Nielson's AutoAudit is a quick and effective way of retro-fitting this to a database. He has a demo screencast here.

Paul Nielson的AutoAudit是一种快速有效的方法,可以将其改编为数据库。他在这里有一个演示截屏。