May 25, 2004 at 6:58 am
I'm looking to run an audit of the tables that are accessed within a specific database. I'd like to do this using SQL Profiler. Essentially all I am looking for is any object that is accessed through a select statement. I've had this running before but lost the script I used to create it and can't figure it out from the profiler gui.
I went to use the Object events, but then remembered that they do not work in 2000, I've done a search, but can't come across anything, does anyone have any ideas?
Thanks.
May 26, 2004 at 5:52 am
Try this one and determine if it answers your question
(copied from a previous post by some one whose name I am sorry to say I have forgotten to that person I apologize)
SELECT a.name, b.name as 'Stored Procedure name' FROM sysobjects a
LEFT OUTER JOIN (sysobjects b left outer join sysdepends on b.id = sysdepends.id) on sysdepends.depid = a.id
WHERE a.xtype = 'u' AND b.xtype = 'p'AND NOT a.name = 'dtproperties'
GROUP BY a.name, b.name "
ORDER BY a.name"
May 26, 2004 at 6:00 am
Unfortunately the data is accessed by users running ad-hoc queries, so that won't help. Thanks anyhow.
May 26, 2004 at 6:18 am
I think that I might have it, I am trapping the object:opened, object:closed, scan:started events and it seems to pull the data I need. It only returns the objectid, but that's not a problem as I will be importing the data into a db ultimately and can join against the sysobjects table for the table names when I do so.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply