CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005t-sql
Michael A
Michael A

Reputation: 9900

Find records modified from a date when no date modified column?

I'm sure there isn't but I was hoping somebody could help me to work out if there's a way that I can see when a table last had a record added to it (only historically with no table modifications being made for future instances, I've already added triggers to the tables for future issues).

We're at the mercy of the historical work of others and unfortunately one of these pieces of work involved not using a datemodified field on many of our tables. I now need to check our schema to find potential tables to de-commission and would like to know if there's a way to do this?

Upvotes: 0

Views: 1119

Answers (2)

jklemmack
jklemmack

Reputation: 3636

You're effectively SOL, but if "close is good enough", look at the sys.dm_db_index_usage_stats and specifically the last_user_update column. That'll tell you the last time something happened that mucked with that index. If you filter to only Primary Key indexes, then that generally means inserted rows. 

select 
so.name, si.name, us.last_user_update
from  sys.dm_db_index_usage_stats us
inner join sys.objects so
    on so.object_id = us.object_id
inner join sys.indexes si
    on  si.index_id = us.index_id
    and si.object_id = us.object_id
where si.is_primary_key = 1

Upvotes: 2

Arnold Zokas
Arnold Zokas

Reputation: 8560

You should be able to use sql server metadata to get this information:

SELECT
    [name],
    create_date,
    modify_date
FROM sys.tables
ORDER BY
    modify_date DESC

Have a look at this for more detailed guides: http://www.google.co.uk/search?q=sql+table+last+modified

Upvotes: 0

Related Questions