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 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@.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 â'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|||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

How do I enumerate the database?

I'm adapting C++ objects to ADO and ADOX for unmanaged targets, and ADOX doesn't appear to be able to fully enumerate the database.

So, I want to know who I can do that in SQL - something SQL92 can handle, so that MySQL, MSSQL2000, Interbase and others will be compatible with the approach.

From ADOX, I can get the entire list of catalogs (databases), then the tables within them, and all fields within the table. What I can't get are the columns within the Indexes or Keys. If I can just figure out how to get that information from SQL, I'd be set.

ADOX has the collections that supposedly provides an enumeration of the "Fields" within the Index, or "Fields" within the key, but attempting to get the "Count" or an "Item" from that collection causes an exception for MySQL and MSDE (MS SQL 2000).

Thanks.SQL Server isn't "object oriented" so you can't enumerate it like classes or collections. Instead, the information is stored in system table in the databases and in the master database. You can get information on fields and indexes from the syscolumns and sysindexes tables. You will have to know (or be prepared to learn) a lot about SQL Server's database engine if you want to do this.

blindman