We can get the dependencies from the execution plan. But this can be achived only with the help of a front end programming language like VB or anything or even simple VB Script that can connect using adodb and then write to a flat file.
I will write the steps
1. Open a connect to the required DB
2. From the connection execute the statement "SET SHOWPLAN_ALL ON"
3. Now run the required Proc py pasing the required parameters
4. it will return a recordset/ dataset depending upon the language used.
5. the value in the Recordset can then be used to find the dependensies with some manipulation.
You can try the same in query analyzer but the problem is you cant manipulate the result set. for this you may have to copy it to a excel File or set the output of the query analyzer to a file.
1. Open query analyzer and connect to the database
2. SET ShowPlan_ALL ON
3. Run the required SP
4. You will get a result with the following columns
The columns of intrest are "Type" , "NodeID", "parentID","StmtText". A few manipulations of these four columns should help you in identifying the dependencies.For SP's the Column "TYPE" will have value "Execute"
Hope this will be usefull for you.
Jomy John Thomas
Your Guide to Admin and Design