问题:

Is there any way to get a table's last modification datetime on Sql Server 2005? Preferably without creating triggers..

And for databases?

EDIT The solution of this question contemplated only CREATE and ALTER modifications. For INSERT, UPDATE and DELETE please look my next question

回答1:

this will list all object types from most recent change to oldest, you can easily modify as necessary...

DECLARE @SearchParams   varchar(500)
SET @SearchParams='yourObjectName'

SELECT
    CONVERT(varchar(23),modify_date,121) AS modify_date
        ,type_desc
        ,name
    FROM sys.objects
    WHERE is_ms_shipped=0 AND name LIKE '%'+@SearchParams+'%'
    ORDER BY modify_date DESC

if you just want tables, try:

DECLARE @SearchParams   varchar(500)
SET @SearchParams='YourTableName'
SELECT
    CONVERT(varchar(23),modify_date,121) AS modify_date
        ,type_desc
        ,name
    FROM sys.objects
    WHERE type='U'
        AND name LIKE '%'+@SearchParams+'%' --can use "=@SearchParams" if you have the entire table name
    ORDER BY modify_date DESC

回答2:

KM's solution will show when a table object (the table definition) was last modified. There is no built-in way to determine when the data within a give row was last updated (or inserted or, of course, deleted). The only 99% reliable way I've ever found to do that is with triggers, with that missing 1% representing when some troublemaker disables the trigger.