How to set Database Audit Specification for all the tables in db

Question

I need to create an audit to track all CRUD events for all the tables in a database , now i have more than 100 tables in the DB , is there a way to create the specification which will include all the tables in the DB ?

P.S : I am using SQL Server 2008

Answer 1

Change Data Capture

You can use the Change Data Capture functionality mechanism provided by SQL Server 2008.

http://msdn.microsoft.com/en-us/library/bb522489.aspx

Note that this will only do Create, Update and Delete.

Triggers and Audit tables

Even for 100 tables, you can use a single script that will generate the audit tables and the necessary triggers. Note, that this is not a very good mechanism - it will slow down the control will not be returned unless the trigger execution is complete.

Answer 2

Found a way to create Database Audit specification , wrote a c# code that dynamically generated sql statement for all the tables and all the actions I needed and executed the resultant string. Frankly the wizard provided is no help at all if you are creating a Database Audit Specification for more than a couple of tables.