How to find which table received an update/insert/delete command-CodePudding

Is there any way to find which table was recently modified (in terms of data) in SQL Server? For example, I am dealing with a badly written code which is very hard to debug, and in order to be able to find what is going on, I would like to create a record via that system and then query what has been changed in the database.

I came across this query

select schema_name(schema_id) as schema_name,
   name as table_name,
   create_date,
   modify_date
from sys.tables
where modify_date > DATEADD(DAY, -30, CURRENT_TIMESTAMP)
order by modify_date desc;

but it doesn't seem to give me what I need. As when I try to manually update and insert records in a table, this query still shows zero results.

I cannot enable the CDC feature on that database.

CodePudding user response：

Finding across database tables for changes, will be very overkill operation, as there will be multiple tables being updated.

If you are having audit columns in the tables, you can find out using the modifedAt column of the table. If you are having audit tables, you can find out the changes happened in table.
If there is no way to find out the table changes, you can do in the below ways. But, if you are monitoring all the tables, it will be huge load on the server.
1. Enable database audit
2. Enable extended events
Reference: Stack Exchange post

CodePudding user response：

You can run a SQL Trace by using SQL Server Profiler, which captures queries being run. The trace have some downsides to query performances, but usually not too bad.

You can for instance filter "TextData"-columns so it only matches specific tables that you're debugging and for example duration > X seconds.

A trace is saved to a file or just displayed in the gui so it doesn't change anything.

People usually say that trace is bad / obsolete, but i like it for debugging performance issues in production where you'd rather not add instrumentation to the code. As long as you don't run it for too long and add good filters like Duration > 1000 ms it's fine.

If you don't have access to Profiler, you can setup trace in SQL code directly (see for instance https://www.mssqltips.com/sqlservertip/1035/sql-server-performance-statistics-using-a-server-side-trace/)