Determining the dependencies of a stored procedure

Question

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 that they call, etc. down the call stack).

If anyone can fabricate such a query, is it additionally possible to determine whether tables are accessed for update, select or delete?

Is it possible to determine the same where views are thrown into the mix?

My stored procs do not contain any dynamically-constructed calls, so that simplifies it at least slightly.

Answer 1

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 ;)

Answer 2

Try this Link:

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

or this to search text:

DECLARE @Search varchar(255)
SET @Search='cost_centre'
SELECT DISTINCT
    o.name AS Object_Name,o.type_desc
    FROM sys.sql_modules        m 
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@Search+'%'
    ORDER BY 2,1