Posted on 2011-10-09
Last Modified: 2012-05-12
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.
Question by:Salman_Ali
    LVL 29

    Accepted Solution

    Could create a cursor
    to loop through all the user tables

    and turn on auditing for each
    LVL 15

    Assisted Solution

    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!
    LVL 4

    Expert Comment

    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.
    LVL 15

    Expert Comment

    This Link will helps you for Auditing using DDL triggers.

    Author Closing Comment

    code would be option to provide

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Suggested Solutions

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now