Hi all,

I have a bunch of undocumented stored procedures (legacy).
What I want, is to find out for each procedure, what changes it makes
in all the database. We want to replace these procedures with some
equivalent (maby sth java-based), and thus I have to know what values
are modified, or inserted to the db.

What is the best approach to track these updates/inserts/deletes done by
stored procedure? Is it possible at all?

Regards,
Chris

I suppose that you can do a code analysis to try to see which procs
are called from your proc, and which tables these modify, etc.

I know of a tool (non-free) that will do this for you and generate a
comprehensive report of the entire SQL code base (well - based on the
code in the database). You can check out www.sqlbrowser.com in case
you're interested.

Michael

On Mar 5, 9:16*am, Krzysztof Adamczyk
<krzysztof_DOT_adamczyk (AT) gmail_CUT_ (DOT) com> wrote:

Thanks for your reply!

mpeppler (AT) peppler (DOT) org [Team Sybase] wrote:
Of course I will do a lot of code analysis, but these procedures are
extremely huge (BTW I'd like to talk to the authors...) and I'm looking
for a tool that will help me with this analysis.

Thanks again - I'll definitely check it out

Greetings,
Chris

On Mar 5, 1:16*am, Krzysztof Adamczyk
<krzysztof_DOT_adamczyk (AT) gmail_CUT_ (DOT) com> wrote:
I think you have to do it by code analysis. Just looking at what a
single run of a proc
does isn't good enough because of conditional logic.

If you have auditing installed/enabled you could try commands like:

use mydb
go
sp_audit "exec_procedure", "all", "myproc",'on'
go
-- do the following for each table, where <operation> = insert/update/
delete
sp_audit "<operation>", "all", "mytbl", "on"
go
select a.spid, a.event, a.eventtime, suser_name(a.suid), dbname,
objname, objowner, extrainfo
from sybsecurity..sysaudits_01 a /* 01 could be a different number */
where eventtime>"a recent time"
and dbname="mydb" and objname not like "sys%"
-- look for events: delete, insert, update, and exec procedure
where event in (18,19,41,42,70,71, 38)
go

and look for insert/update/deletes which occur at almost the same time
as an exec procedure, with the same spid. You could probably write
some sort of fancy synchronized subquery to extract what you're
looking for.

This will generate a lot of audit logging which could slow down your
server. Depending on how you have auditing setup, the audits logs
could be archived to a repository where you can run these queries. If
audit logs are setup to reuse themselves, you'll have to run the
queries before the audit log gets reused/overwritten.

Not particularly easy, but doable.

Ben Slade
Chevy Chase, MD

On Mar 5, 4:16*am, Krzysztof Adamczyk
<krzysztof_DOT_adamczyk (AT) gmail_CUT_ (DOT) com> wrote: