SQL Server 2012 Data Change Tracking We currently have a front end application that is business critical and uses Microsoft SQL Server as the database provider. I have been asked to create another system that mimics some of the functions of this application and will insert/modify data in exactly the same way.

The only reason we are looking at doing this custom is because the developer does not provide any sort of API or assistance in manual data entry of their tables. Please note, we own the databases and all the data on the system but we still need the application to perform the day to day tasks.

I am looking for ways to track all changes made in a database (insert/update/delete) on all tables. I have a fresh copy of the system and a blank database to test, to give an example, I would generate a Purchase Order and then find out which tables were modified and what data was added so I could replicate in my code.

I know SQL has Data Change Tracking; however, I cannot see a way to easily implement this on all columns and tables within a database.

I am interested in any recommendations of third party applications or a walkthrough on how to utilise Microsoft's built in functions, appreciate the help if any.
I would recommend to script a loop or cursor to enable this on all of your databases and tables. It will capture all of the columns automatically. 

Example of loop CDC Note: As always with code on the internet, evaluate cautiously and promote through personal and dev environments prior to production.


  By default, all of the columns in the source table are identified as captured columns. If only a subset of columns need to be tracked, such as for privacy or performance reasons, use the @captured_column_list parameter to specify the subset of columns.


CDC Configuration MSDN

I think it would be better to run SQL Profiler though to catch the code that is running and re-create it that way however. CDC will show you the end results, not how it gets from A to C.