ashish_gupta

Hi,

Recently, I was told to make some kind of do***entation for a database
which is in production but does not have any do***ents.

There are a total of 141 stored procs and 203 tables.
I could figure out that about 91 stored procedures are run every
morning.

I can also extract all the definitions using DBArtisan.
But I really do not want to go through the definitions of all these
stored procedures.

Is there a way I can "reverse engineer" and get information such as
this :
(I.) for each stored procedure,
(a) which stored procedures does it call
(b) which stored procedures call it
(c) which tables does is Read from, Write to

(II.) can the relationships between stored procedures be made
graphical ?

Thanks and regards,
Ashish

ashish_gupta

Hi,

Recently, I was told to make some kind of do***entation for a database
which is in production but does not have any do***ents.

There are a total of 141 stored procs and 203 tables.
I could figure out that about 91 stored procedures are run every
morning.

I can also extract all the definitions using DBArtisan.
But I really do not want to go through the definitions of all these
stored procedures.

Is there a way I can "reverse engineer" and get information such as
this :
(I.) for each stored procedure,
(a) which stored procedures does it call
(b) which stored procedures call it
(c) which tables does is Read from, Write to

(II.) can the relationships between stored procedures be made
graphical ?

Thanks and regards,
Ashish

david owen

This information is held in sysdepends and is usually accessed by sp_depends
proc, which will give you the information. Should be a relatively simple
job to knock up a script to do that for each table.
1> sp_depends get_apptable1
2> go
Things the object references in the current database.
object type updated
selected
---------------------------------------- ---------------- ----------
----------
dbo.mark_msg_read stored procedure no
no
dbo.apptable1 user table no
no
(return status = 0)
1> sp_depends mark_msg_read
2> go
Things the object references in the current database.
object type updated
selected
---------------------------------------- ---------------- ----------
----------
dbo.appkey8 user table no
no
dbo.appkeyA user table yes
yes
dbo.get_now_for_appkey8 stored procedure no
no
Things inside the current database that reference the object.
object type
---------------------------------------- ----------------
dbo.get_apptable1 stored procedure
dbo.get_apptable2 stored procedure
dbo.get_apptable3 stored procedure
dbo.get_in_text stored procedure
dbo.process_empty stored procedure
dbo.process_hours_of_service stored procedure
dbo.process_load_at_shipper stored procedure
dbo.process_pre_assign_ack stored procedure
dbo.process_request_directions stored procedure
dbo.process_trip_complete stored procedure (return status = 0)
1>

Not aware of a tool. PowerDesigner *may* be able to do this.

dowen
--
David Owen Midsomer Consultants Inc. dowen@midsomer.org
Maintainer of the Sybase FAQ: http://www.isug.com/Sybase_FAQ

david owen

This information is held in sysdepends and is usually accessed by sp_depends
proc, which will give you the information. Should be a relatively simple
job to knock up a script to do that for each table.
1> sp_depends get_apptable1
2> go
Things the object references in the current database.
object type updated
selected
---------------------------------------- ---------------- ----------
----------
dbo.mark_msg_read stored procedure no
no
dbo.apptable1 user table no
no
(return status = 0)
1> sp_depends mark_msg_read
2> go
Things the object references in the current database.
object type updated
selected
---------------------------------------- ---------------- ----------
----------
dbo.appkey8 user table no
no
dbo.appkeyA user table yes
yes
dbo.get_now_for_appkey8 stored procedure no
no
Things inside the current database that reference the object.
object type
---------------------------------------- ----------------
dbo.get_apptable1 stored procedure
dbo.get_apptable2 stored procedure
dbo.get_apptable3 stored procedure
dbo.get_in_text stored procedure
dbo.process_empty stored procedure
dbo.process_hours_of_service stored procedure
dbo.process_load_at_shipper stored procedure
dbo.process_pre_assign_ack stored procedure
dbo.process_request_directions stored procedure
dbo.process_trip_complete stored procedure (return status = 0)
1>

Not aware of a tool. PowerDesigner *may* be able to do this.

dowen
--
David Owen Midsomer Consultants Inc. dowen@midsomer.org
Maintainer of the Sybase FAQ: http://www.isug.com/Sybase_FAQ

ashish_gupta

David,

thanks for your reply.

I had thought of using sp_depends or the sysdepends table directly,
but was not sure whether I could rely on this table ...

It seems that, sometimes, if the stored procedures are created in the
"not correct" order, sysdepends does not get updated.

One method is to recreate all the stored procedures in the "correct"
order ... I have a tool - DBArtisan - for that, but was hoping to
extract good info without doing *anything* .... :-)

thanks,
ashish

david owen

You certainly can rely on sysdepends. If not there must be a bug in the
release of ASE you are using. I suspect that this comes under the "myths
and legends of a database server".

"It seems", do you mean you have an actual case that you could log with
Sybase where it does not get updated?? What ASE version? Which platform?
I would be interested in the answer.


Two points. Firstly, you have more faith in DBArtisan than I do. It must
also be a newer version that the last one I used (version 6.something)
since it could only extract the stored procs in either sysobjects order (ie
based on crdate) or in alphabetical order. I admit that this may have
changed with version 7 (if they have released a version of 7 which can
connect to Sybase). My second point is this. If they have updated the
software, where do you think they are getting their information from? It
is either sysdepends *or* they are searching through syscomments in order
to determine the extraction order. I suspect that for even a small(ish)
number of tables the latter would take a significant amount of time.

dowen
--
David Owen Midsomer Consultants Inc. dowen@midsomer.org
Maintainer of the Sybase FAQ: http://www.isug.com/Sybase_FAQ

ashish_gupta

David,

thanks for your reply.

I had thought of using sp_depends or the sysdepends table directly,
but was not sure whether I could rely on this table ...

It seems that, sometimes, if the stored procedures are created in the
"not correct" order, sysdepends does not get updated.

One method is to recreate all the stored procedures in the "correct"
order ... I have a tool - DBArtisan - for that, but was hoping to
extract good info without doing *anything* .... :-)

thanks,
ashish

michael peppler

If you create a proc that depends on another proc that doesn't exist yet
then sysdepends doesn't get updated.

So sysdepends is not necessarily 100% correct.

Michael
--
Michael Peppler Data Migrations, Inc.
mpeppler@peppler.org http://www.mbay.net/~mpeppler
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.mbay.net/~mpeppler/resume.html

larry coon

Right -- I've seen ASE messages to the effect of, "unable to
update sysdepends" when I've been migrating procs using DB Artisan.
As a result, sysdepends gets out of sync with the data. I don't
think DB Artisan (even the latest version) does any kind of checking
to determine the order in which procs should be migrated.


Larry Coon
University of California

The NBA Salary Cap FAQ:
http://members.cox.net/lmcoon/salarycap.htm

david owen

You certainly can rely on sysdepends. If not there must be a bug in the
release of ASE you are using. I suspect that this comes under the "myths
and legends of a database server".

"It seems", do you mean you have an actual case that you could log with
Sybase where it does not get updated?? What ASE version? Which platform?
I would be interested in the answer.


Two points. Firstly, you have more faith in DBArtisan than I do. It must
also be a newer version that the last one I used (version 6.something)
since it could only extract the stored procs in either sysobjects order (ie
based on crdate) or in alphabetical order. I admit that this may have
changed with version 7 (if they have released a version of 7 which can
connect to Sybase). My second point is this. If they have updated the
software, where do you think they are getting their information from? It
is either sysdepends *or* they are searching through syscomments in order
to determine the extraction order. I suspect that for even a small(ish)
number of tables the latter would take a significant amount of time.

dowen
--
David Owen Midsomer Consultants Inc. dowen@midsomer.org
Maintainer of the Sybase FAQ: http://www.isug.com/Sybase_FAQ