?
Solved

list most recent modified or updated tables in MS SQL 2008

Posted on 2011-05-12
7
Medium Priority
?
502 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:solarisinfosys
7 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35751862
select O.name as tableName, O.create_date, O.modify_date
from sys.objects O
where O.type = 'U'
order by 3
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35751864
Above I assume by "modified" you mean that the table definition has changed, not that the data in the table has changed.
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 35751871
At data/records level, you can't get that information..
But you can find the last time when your table structure is modified by running the query below:

select name, modify_date
from sys.tables
where name = 'ur_table_name'
and modify_date between x and y
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 9

Expert Comment

by:kaminda
ID: 35751888
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

0
 
LVL 9

Expert Comment

by:kaminda
ID: 35751903
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)
0
 
LVL 9

Expert Comment

by:kaminda
ID: 35751927
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.
0
 

Author Closing Comment

by:solarisinfosys
ID: 35752130
Thanks that worked perfectly
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Integration Management Part 2
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question