1

I am using a SQL Server.I have more 1000 tables in a database which i want to track for data changes.I have read about CDC(Change Data Capture) and will enable it on all the 1000 tables.It creates multiple cdc tables to keep track of changed data.

But the problem here is on daily basis less than 200 tables are gonna be modified. But still i have to check all the 1000 tables cdc.[capture_instance]_CT tables to find if new data is been added to that particular table.

So is there a way i can find the list of tables which are modified with new data only.

Thanks in advance.

Improve this question

2 Answers 2

Reset to default
1

You can configure SQL Server Auditing (Server and Database Audits), you have the ability to audit CRUD operations as well as other activity. One warning is to be aware of how much data you are auditing, logs fill up fast.

Improve this answer
0

I had once used a logging mechanism on master data tables from different databases on a server illustrated at SQL Server Log Tool for Capturing Data Changes Unfortunately, this method creates SQL triggers on tracked db tables, so it might affect the performance in a negative way when compared with CDC. If the tables or the transactions on tables which you want to track is limited then you can think of applying this solution.

If you were working on SQL Server 2016, you might be using temporal tables and query temporal table for changes on a given date in past

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.