Determining The Dependencies Of A Stored Procedure.

Is there a way (or, ideally, a query) to determine all the tables that a stored procedure accesses, including those accessed by other stored procs that it calls itself (and those t

Solution 1:

Try this Link:

how-to-find-all-dependencies-of-a-table-in-sql-server

or this to search text:

DECLARE@Searchvarchar(255)
SET@Search='cost_centre'SELECTDISTINCT
    o.name AS Object_Name,o.type_desc
    FROM sys.sql_modules        m 
        INNERJOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like'%'+@Search+'%'ORDERBY2,1

Solution 2:

i've got no access to sql-server at the moment, but i know you can check dependecies - i've done this, determining views accessing tables.

have a look at sys.all_objects and sysdepends. you can join them on object_id and depid. including the type-column of sysdepends, you should be able to get the tables, accessed by a SP.

will have a look tomorrow, if still neccessary - but you should be able to get your information checking the above infos!

@edit: just saw comment of Aaron. Everything explained right there. Perhaps it was luck to get the right dependencies in my case ;)