asked on

list most recent modified or updated tables in MS SQL 2008

How do i know which tables got modified or updated in SQL Server when any
update or modification is done from an End User using an Application Program Or What SQL
Statement should i use to list all the tables that have been modified on a given range of date
and time.

knightEknight

select O.name as tableName, O.create_date, O.modify_date
from sys.objects O
where O.type = 'U'
order by 3

knightEknight

Above I assume by "modified" you mean that the table definition has changed, not that the data in the table has changed.

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kaminda

You can simply query the sys.tables to get these information

USE DatabaseName
Go

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

Open in new window

kaminda

If it is data changes you want you can

1. Run a profiler and keep monitoring the data changes. This needs lot of disk space and there can be a perfomance hit on your server.

2. You can read your transaction log using a tool such as Apex SQL Log (http://www.apexsql.com/sql_tools_log.aspx)

kaminda

Adding to the list there is another way you can get somewhat relavant details using Index statistics DMV. This works only for the tables and columns having indexes because these are index data update statastics.

select o.name, max(u.last_user_seek) as LastSeek, max(u.last_user_scan) as LastScan, max(u.last_user_lookup) as LastLookup, max(u.last_user_update) as LastUpdate from sys.dm_db_index_usage_stats u inner join sys.objects o on o.object_id = u.object_id where o.type = 'U' and o.type_desc = 'USER_TABLE' group by o.name

FYI : LastSeek, LastScan and LastLookup all are reads and LastUpdate is updates.

solarisinfosys

ASKER

Thanks that worked perfectly