• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

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

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?

2 Solutions
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.


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:
FROM fn_trace_gettable(‘D:\SQLTrace\TraceGrouptableAccessFile.trc’, default) Trace

Vinod Pottekkatt
jruheAuthor Commented:
Thanks guys.  Both have value.
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now