How to get all the transaction logs (insert update delete) for a specific table in SQL Server 2008

sql-server-2008 audit-logging
hasan lamaa picture hasan lamaa · Oct 16, 2011 · Viewed 29.6k times · Source

I want to get all the transactions applied on a specific table in SQL Server 2008.

I found the last time a table was updated using this script:

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'DBName')
AND OBJECT_ID=OBJECT_ID('tableName')

I want to know all the transactions (Inserts, Updates, Deletes) for that table, and their datetime, and the query applied.

What is the best way to do this?

Answer

Vali Alexandrescu picture Vali Alexandrescu · Sep 17, 2013

The only way to do this in a reasonable amount of time is to use a third party tool(as Martin said in first comment) such as ApexSQL Log that can read transaction log and get the information you need.

Note that in order for this to work your database has to be in a full recovery mode because that’s when SQL Server logs full transaction details that can be reconstructed later.

Another option is to investigate how to use undocumented fn_dblog function but this will take you a lot more time and you won’t be able to read detached logs or transaction log backups.

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More