SQL Server: How to know when a table has been queried

Posted on 2011-10-04
Last Modified: 2012-05-12
I am working with a database which exists off a commercial product.  It has "Group" tables that are populated when users add groups via the front end, and they may use their own groups or groups that other users have made.  I have a large client who would like to remove unused groups.  Nothing changes in the tables when the groups are being used.

Triggers don't work on the "select" command.  Is there a way that I may add something (back-end only, sadly) that would keep track of when a group was used?

Question by:jruhe
    LVL 21

    Accepted Solution

    This probably isn't going to be easy to do.  You can setup a profiler trace, but as far as I know you can't specify a specific table that you are looking for.  A table could be referenced by a stored procedure or function.  You would need a list of every object that references the table and then compare that to the profiler trace and see who executed it.  That would be a monumental task.  

    But I have an easier solution.  Disable, not delete, the accounts in question and see who screams.  Crude, but probably more effective.


    LVL 3

    Assisted Solution

    You Can use SQL Profiler for tracing tables activity such as selects, inserts, deletes and updates.
    -- remember to Apply filters like
        1) on Group table names
        2) dbid -- target database

    Review the trc output by querying through TSQL function:
    SELECT *
    FROM fn_trace_gettable(‘D:\SQLTrace\TraceGrouptableAccessFile.trc’, default) Trace

    Vinod Pottekkatt
    LVL 4

    Author Comment

    Thanks guys.  Both have value.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    728 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

    20 Experts available now in Live!

    Get 1:1 Help Now