How do I get to identify all tables accessed?

How do I get to identify all tables accessed during a particular period of
usage?
Used SQL Profiler and set up the following:
Objects: Object:Closed & Object:Opened.
TSQL SQL: BatchStarting & SQL:StmtCompleted.
Columns:
DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
Filter: Applied a filer to the databaseId as I wanted to identify only
tables accessed in a particular DB.
But on running a simple SQL SELECT – the following columns “DatabaseName
,
ObjectId and ObjectName” columns were empty.
Why is this?
Please let me know what I’m missing out on?
(Note: A similar question had been posted earlier on – but there was no
satisfactory answer to it.)
Cheers!
SQLCatzThis is what Books Online has to say about Object:Closed and Opened:
The event classes Object:Closed and Object:Opened are provided for running t
races on SQL Server 7.0
and earlier. These objects do not exist in SQL Server 2000.
What events did you get in the trace? If you get TSQL SQL: BatchStarting & S
QL:StmtCompleted, then
you can't expect to see any object etc.
Consider capturing the Execution Plan event. You can filter on particular ta
bles using the TextData
column. this is so far the only reliable way I found to audit access for a s
et of tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:82798F82-2F21-44B8-B0D7-926DB4F8CBF5@.microsoft.com...
> How do I get to identify all tables accessed during a particular period of
> usage?
> Used SQL Profiler and set up the following:
> Objects: Object:Closed & Object:Opened.
> TSQL SQL: BatchStarting & SQL:StmtCompleted.
> Columns:
> DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
> Filter: Applied a filer to the databaseId as I wanted to identify only
> tables accessed in a particular DB.
> But on running a simple SQL SELECT – the following columns “DatabaseNa
me,
> ObjectId and ObjectName” columns were empty.
> Why is this?
> Please let me know what I’m missing out on?
> (Note: A similar question had been posted earlier on – but there was no
> satisfactory answer to it.)
> Cheers!
> SQLCatz|||Hello Tibor,
Thank you for the prompt reply!
I do not want to filter on certain tables - want to get all the tables that
the t-sql statements access while a set of scripts is running. In order to
filter the tables - I need to have all their names. In this case - I do not
have the complete list. Was hoping that by running the profiler - I'd be abl
e
to get all the tables that were accessed.
Cheers!
SQLCatz