|
Home > Archive > MS SQL Server > July 2005 > How do I get to identify all tables accessed?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How do I get to identify all tables accessed?
|
|
| SQLCatz 2005-07-12, 3:23 am |
| 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!
SQLCatz
| |
| Tibor Karaszi 2005-07-12, 3:23 am |
| This is what Books Online has to say about Object:Closed and Opened:
The event classes Object:Closed and Object:Opened are provided for running traces 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 & SQL:StmtCompleted, then
you can't expect to see any object etc.
Consider capturing the Execution Plan event. You can filter on particular tables using the TextData
column. this is so far the only reliable way I found to audit access for a set 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@microso
ft.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 “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!
> SQLCatz
| |
| SQLCatz 2005-07-12, 7:23 am |
| 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 able
to get all the tables that were accessed.
Cheers!
SQLCatz
|
|
|
|
|