Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2003-12-01
15
Medium Priority
?
417 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 

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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

WooCommerce is becoming the most powerful e-commerce plugin for Wordpress. And why not. The platform comprises of numerous core plugins that may come in handy, powerful options to make your website development task much easier.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

604 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