How to Identify in how many Storedprocedures hasa sp been called?

Question

How to Identify in how many Storedprocedures hasa sp been called?

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Grant Fritchey (9/19/2008)
Chirag (9/19/2008)
GilaMonster (9/18/2008)
Chirag (9/18/2008)
another way is to right click on the proc in SSMS and select view dependencies. It will show all objects that depend on the proc and also all objects that the proc depends on.
sysdepends isn't always accurate, especially in cases where the outer proc was created before the inner procs.
Yes you are right. They have corrected this in SQL Server 2008 though.
MS doesn't want us using sysdepends any more though, right? sys.sql_expression_dependencies, sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities are all the new mechanisms for dependency tracking.

Yup. Just ran the profiler and did selected the option view dependencies in SQL 2008. It does not use Sysdepends but all the system views that you mentioned.

"Keep Trying"

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

Jeff Moden (9/19/2008)
Chirag (9/18/2008)
another way is to right click on the proc in SSMS and select view dependencies. It will show all objects that depend on the proc and also all objects that the proc depends on.
I'll have to double check, but I don't believe that's true. I believe it shows only the first level dependencies. If a ProcA calls ProcB which calls ProcC, I'm pretty sure that ProcC won't show up in the dependencies list.
Umm... kind of and not really... it shows only the first level dependencies. If a ProcA calls ProcB which calls ProcC, ProcC won't show up in the dependencies list for ProcA.

it does show. view the option objects on which [a] depends.

"Keep Trying"

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Not sure what you mean... what did you do to get it to show ProcA has a ProcC dependency? I can only see ProcB. Maybe I'm doing the bloody thing backwards 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

Jeff Moden (9/23/2008)
Not sure what you mean... what did you do to get it to show ProcA has a ProcC dependency? I can only see ProcB. Maybe I'm doing the bloody thing backwards 😛

Check out the attachment. Its a mspaint file which i have zipped. May be i have misunderstood some thing.

Edit:- I have assumed that procedures have been created in correct order. i.e. if proc a calls proc b & proc b calls proc c then proc c is created first then proc b and then proc a.

"Keep Trying"

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Dang... thanks, Chirag... I keep forgeting this is a 2k5 forum. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions