I have 806 tables in a database. How to turn on select, insert, update, delete auditing on all table at once instead of selecting individual tables.
Who is Participating?
QPRConnect With a Mentor Commented:
Could create a cursor
to loop through all the user tables

and turn on auditing for each
AnujConnect With a Mentor SQL Server DBACommented:
Audinting the SELECT statement is only supported in SQL Server 2008 Enterprise Edition.

If you are on SQL Server 2008 Enterprise Edition, you can use the inbuilt Auditing feature, It supports INSERT, UPDATE, DELETE, UPDATE and SELECT. If you are on any other SQL Server Version you must create custom DML triggers on each tables, still you cannot audit the SELECT statements.

The other option is to make use of SQL Server Profiler to capture All the select statement and write them to a output  table later you can query the Profile out put table, But this is not designed for Auditing since this have performance overhead!
you can use sp_msforeachtable(in-built procedure) to iterate through all tables.

run the following command to see space used by each table in a database

EXEC sp_msforeachtable 'sp_spaceused ''?'''

for more detail search this on MSDN.
AnujSQL Server DBACommented:
This Link will helps you for Auditing using DDL triggers.
Salman_AliAuthor Commented:
code would be option to provide
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.