Hi all,

Is there anyway, I can determine the tables and views accessed by a stored procedure ?

I have to create a dependancy list of stored procedures and tables / views of a database containing more than 1000 SPs. I am looking for an automatic way in which I can do this.

thanks

Madhu

use pubs
exec sp_depends 'byroyalty'

use northwind
exec sp_depends 'dbo.CustOrdersDetail'

or take a look at the sysdepends tables


“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/

Thank you very much. You helped me save a lot of time. Also taught me a smart technique.

One doubt. I have an SP which just fetches all records from a table depending on a condition. When I run sp_depends on this SP, it gives the list of display columns mentioned in the query. However, the "selected" column in the result is "no" always. Shouldn't it be yes for those columns which are selected in the query?

I am using SQL Server 2000.

Thanks

Madhu

There a couple of problems with relying on sysdepends
1) If you truncate a table in a stored procedure but don't make any other reference to it then there is no dependency
2) If when you modify views, functions, tables, and stored procedures you drop and then create them, you will inevitably eliminate some of the depenency information unless you happen to recreate things in just the right order.
see http://www.sqlservercentral.com/colu...pendencies.asp


David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com