CODEWITHSUNDEEP
sql-serversql-server-2005t-sql
Jader Dias
Jader Dias

Reputation: 90465

How to get last modification datetime on Sql Server 2005?

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

Upvotes: 1

Views: 1820

Answers (2)

KM.
KM.

Reputation: 103579

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

Upvotes: 1

Philip Kelley
Philip Kelley

Reputation: 40289

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.

Upvotes: 1

Related Questions