How to Know what tables get affected for a given process??

Hi ,
Is there any command or any setting that I have to do so that I know what tables are being accessed for a given process?

I have a set of processes that run against the Sybase database and Select/Update/Insert various tables. I want to know what all tables are being accessed for a particular process.

E.g:
Suppose I run the Payments process, this will select from a table called tbl_invoice etc,
and Insert/Update tbl_payment etc.
I want to get the list of all the tables that this process accesses, i.e what are the tables that it Selects, what tables does it Inserts into and what are thte tables that this will Update.

Is there any setting which I can set and then run the Payamnet process so that at the end of the process I have the above said list. Something similar to profiler.

Thanks in advance,

hkprs
hkprsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sandhiyaaCommented:
Hi ,
u can do this either by putting sp_depends < procedure name>
or showplan on and see what are the tables it is accessing while eecuting the procedure.
0
hkprsAuthor Commented:
Hi Sandhyiyaa,

Thanks for the suggestion. But I am not just executing one procedure or one query. I have a exe that I run using a Unix script file. The exe is generated using some CPP files. The CPPs have many Sql statements including Selects, Updates and Inserts. It even has some procedures.

Now , what I want is that after I run the Unix script file, I want to know all the tables that have been accessed by all the queries and procedures present in the CPPs.

In other words, starting from a particular instance, any hit on the databse should be recorded with details of the tables that have been accessed and if it is a select/Update/Insert.

Is this possible?

Thanks & Regards,
hkprs
0
hkprsAuthor Commented:
Can I use TRACEON to achive my objective? If yes, How??
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

ChrisKingCommented:
do you have the ability to modify the CPP code ?
if so, you should be able to add some logging into the application's function for executing a SQL statement.
0
ChrisKingCommented:
Yous still have 5 open questions from February to April
How about you pay some attention to those first !!!!!!

04/24/2003 100 table to file
04/04/2003 50 Use of GROUP BY Clause!
03/20/2003 50 Query Running for Long time!!
03/13/2003 125 Error While Using Optimizer hint in From...
02/17/2003 50 SPMIG_ERROR while Compling StoredProcs  
0
hkprsAuthor Commented:
I'd love to but I am not able to find a way how I can close a question without accepting any comment as an answer!!!

Now, reg the question, I didn't get what you suggested. The CPP might call a procedure and that might Select/Insert/Update into some table. How can I get this info from  a CPP file???

I heard that in MS SQL profiler, we have the option of tracing a process! Is smoething similar present in Sybase too.

Regards,
hkprs
0
ChrisKingCommented:
lets say the CPP calls a function for each SQL statement it is executing

eg.    retval = SQLExecuteCommand( "SELECT blaa FROM some_table" );

then add to the "SQLExecuteCommand()" function a logging command and log every command to a log file on the file system.
This is NOT a sybase change, its a extention of the application

RE CLEANUP:
if you don't like the answers you are getting, tell us that we are on the wrong track.
if you don't give any feedback (and you didn't), we can't help !
If you feel that you still have a valid case, put a request into Community Support for a refund
http://www.experts-exchange.com/Community_Support/ 
0
hkprsAuthor Commented:
Thanks for your suggestion,
but if the following is the case:

retval = SQLExecuteProc( "proc_xyz" );

the proc 'proc_xyz' might contain any Inserts/Updates/Deletes on any tables and I can't log  in the application's log.

Thanks,
hkprs
0
ChrisKingCommented:
then combine the list of proc calls with sandhiyaa's comment and run a sp_depends on the proc's in the list
0
LowfatspreadCommented:
Hi hkprs,
the sp_depends wont of course be able to identify any dynamic sql that is issued.

Cheers!
0
ChrisKingCommented:
hkprs
any updates ?
0
hkprsAuthor Commented:
Hi all,
Sorry for delay in updation. Client didn't approve of the idea that was put forth. There are about 150 cpps and a major code modification is required. So, client wants us to look into all the CPPS and find the indtances of tabels being used.

Now can anyone tell me how close this question???

Thanks a lot for all the suggestions!!!

Regards,
Hkprs
0
ChrisKingCommented:
just award points to with a "accept answer" / "assisted answer" buttons
or you can request a refund by posting in "community support" if you feel that you did not get an answer to your question
0
Computer101Commented:
PAQed, with points refunded (500)

Computer101
E-E Admin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.