Very Computer

I need to generate a cross reference of all (user-defined)
stored procedures & tables, i.e. which tables are referenced
by every stored procedure (first part), and which stored
procedures refer to it for each table (second part).

Is there an automatic way to do this instead of manually
examining the codes of stored procedures?

Thanks,
Iwan

Look at sysdepends. You will need to play around (link) with sysobjects .

Quote:> I need to generate a cross reference of all (user-defined)
> stored procedures & tables, i.e. which tables are referenced
> by every stored procedure (first part), and which stored
> procedures refer to it for each table (second part).

> Is there an automatic way to do this instead of manually
> examining the codes of stored procedures?

> Thanks,
> Iwan

sql2000

right click on the stored procedure
select all tasks/display dependencies

Quote:> I need to generate a cross reference of all (user-defined)
> stored procedures & tables, i.e. which tables are referenced
> by every stored procedure (first part), and which stored
> procedures refer to it for each table (second part).

> Is there an automatic way to do this instead of manually
> examining the codes of stored procedures?

> Thanks,
> Iwan

I tried that, but all I could see are "system" objects (exist in master),
and I couldn't
see anything that is defined within the database I created.  BTW, I'm using
SQL7.

Iwan

    Look at sp_depends system SP in BOL. There are several gotchas if you
want to check dependencies in SQL Server in general. For example, if you
create a dependant object after the parent object, this SP will not return
any data.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
(Please reply only to the newsgroup)

There is at least one product that will do what you want (sysdepends
does not definitely keep all dependancies).  The product is CAST
Workbench - I evaluated a while ago.  It actually analyses the code and
builds up dependancies detween all database objects (can actually work
with client code as well - VB, VC++ etc).

Tim

Sent via Deja.com http://www.deja.com/
Before you buy.

Iwan,

You can do either of the following steps:
1)  In the Enterprise Manager, right click on the required object, -  All
Tasks  -  Display Dependencies.

2)  If you wish to check the dependencies in a SQL code, then you can use
the following:
USE database_name
GO
sp_depends object_name
GO

One of the mails mentioned that if you make the child object first and then
the parent object,  the sp_depends does not show that in the dependencies.  
 That is not completely true.  SQL Server does not recognize such a
dependency at all, until you re-run that dependency again.    To
demonstrate my point, run the following line by line.

create procedure try as  select * from authors1
sp_depends try
sp_depends authors1
create table authors1 (col1 int)
sp_depends try
sp_depends authors1
create procedure try2 as  select * from authors1
sp_depends try
sp_depends try2
sp_depends authors1
select * from sysdepends where id = object_id('try')
select * from sysdepends where id = object_id('try2')
select * from sysdepends where id = object_id('authors1')
select * from sysdepends where depid = object_id('authors1')

3)  If you want to write a code that is suitable to your scenario
specifically, see the code of sp_depends ( using  "sp_helptext sp_depends")
 .  That will help you understand the sp_depends stored procedure, and will
enable you to design your own stored procedure.   It does reference the
sysdepends system table that is available for each database.

Please contact me by e-mail if you have any further questions.

Regards,
Rishi Agrawal, MCSE
MS SQL Server Support - Las Colinas, TX
Phone: 469.775.6811

Office Hours: 9:00am - 6:00 pm Central Time

--------------------

| Subject: Help! How can I create references of all stored procedures
| Date: Thu, 9 Nov 2000 10:54:48 -0800
| Lines: 12
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400

| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: fw-1-bvtn.integratelecom.com 206.163.82.5
| Path: cppssbbsa01.microsoft.com!cppssbbsa05
| Xref: cppssbbsa01.microsoft.com
microsoft.public.sqlserver.programming:127820
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| I need to generate a cross reference of all (user-defined)
| stored procedures & tables, i.e. which tables are referenced
| by every stored procedure (first part), and which stored
| procedures refer to it for each table (second part).
|
| Is there an automatic way to do this instead of manually
| examining the codes of stored procedures?
|
| Thanks,
| Iwan
|
|
|