How to list tables where data was inserted deleted or updated in last week

Question

I have sql server 2008 databases, I would like to know which tables was updated last week i.e. tables which has new rows, updated existing rows or which rows are deleted.

Is there any way to do this for existing database.

Answer 1

Try this one -

SELECT 
      [db_name] = d.name
    , [table_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , s.last_user_update
FROM sys.dm_db_index_usage_stats s
JOIN sys.databases d ON s.database_id = d.database_id
JOIN sys.objects o ON s.[object_id] = o.[object_id]
WHERE o.[type] = 'U'
    AND s.last_user_update IS NOT NULL
    AND s.last_user_update BETWEEN DATEADD(wk, -1, GETDATE()) AND GETDATE()

Answer 2

Try with Change Data Capture. It's a good way to keep track of your change on the DB. You have to enable the feature on one or more DBs, then on one or more table (it's a Table feature, so you will do it for every table you need).

---

Enable CDC on database.

Let's assume we want to enable CDC for AdventureWorks database. We must run the following SP to be sure this feature will work:

USE AdventureWorks 
GO 
EXEC sys.sp_cdc_enable_db 
GO

As result, we'll find a new schema called cdc and several tables automatically added:

• cdc.captured_columns – This table returns result for list of captured column.
• cdc.change_tables – This table returns list of all the tables which are enabled for capture.
• cdc.ddl_history – This table contains history of all the DDL changes since capture data enabled.
• cdc.index_columns – This table contains indexes associated with change table.
• cdc.lsn_time_mapping – This table maps LSN number and time.

---

Enable CDC on table.

After having enabled CDC on desired DB(s) it's time to check if there are tables with this feature on:

USE AdventureWorks 
GO 
SELECT [name], is_tracked_by_cdc  
FROM sys.tables 
GO

If not, we can enable the changes capture for HumanResources.Shift table with the following procedure:

USE AdventureWorks 
GO 
EXEC sys.sp_cdc_enable_table 
@source_schema = N'HumanResources', 
@source_name   = N'Shift', 
@role_name     = NULL 
GO

Be sure you SQL Agent is up and running because it will create a job (cdc.AdventureWorks_capture probably) to catch the modifications. If all procedures are correctly executed we'll find a new table called cdc.HumanResources_Shift_CT, among the system tables, containing all the HumanResources.Shift changes.

Note: be careful with @role_name parameter, it specifies database infos access.