Status: Solved
Priority: Medium
Security: Public
Views: 387
Last Modified: 2012-05-12

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?

Thanks
jr

jruhe

Asked: 2011-10-04

Advertise Here

2 Solutions

LVL 21

JestersGrindCommented: 2011-10-04

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.

Greg

LVL 3

pg_vinodCommented: 2011-10-04

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

HTH,
Vinod Pottekkatt

LVL 4

jruheAuthor Commented: 2011-10-05

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.

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

Join & Write a Comment Already a member? Login.

Featured Post