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

Try this one -

SELECT       [db_name] = d.name    , [table_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name    , s.last_user_updateFROM sys.dm_db_index_usage_stats sJOIN sys.databases d ON s.database_id = d.database_idJOIN 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()

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.

There is no way to find out this info by default unless you already have some auditing system installed.

Only option, assuming your database is in full recovery mode, is to read transaction log and try get info from there.

You can try reading transaction log using sql server functions DBCC LOG and fn_dblog or using third party tools such as ApexSQL Log.