Solved

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

Posted on 2003-12-01
15
322 Views
Last Modified: 2012-05-04
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
0
Comment
Question by:hkprs
15 Comments
 
LVL 1

Expert Comment

by:sandhiyaa
ID: 9856471
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
 

Author Comment

by:hkprs
ID: 9856580
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
 

Author Comment

by:hkprs
ID: 9857099
Can I use TRACEON to achive my objective? If yes, How??
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9857104
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
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9857117
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
 

Author Comment

by:hkprs
ID: 9857202
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
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9857249
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:hkprs
ID: 9857448
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
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9857475
then combine the list of proc calls with sandhiyaa's comment and run a sp_depends on the proc's in the list
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9864585
Hi hkprs,
the sp_depends wont of course be able to identify any dynamic sql that is issued.

Cheers!
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9963490
hkprs
any updates ?
0
 

Author Comment

by:hkprs
ID: 9969690
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
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9969905
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
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 11535077
PAQed, with points refunded (500)

Computer101
E-E Admin
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

HOW TO: Connect to the VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere (HTML5 Web) Host Client 6.5, and perform a simple configuration task of adding a new VMFS 6 datastore.
HOW TO: Upload an ISO image to a VMware datastore for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere Host Client, and checking its MD5 checksum signature is correct.  It's a good idea to compare checksums, because many installat…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now