Does SQL Prompt Have a Way to show Stored Procedure hierarchies

EdCarden

What exactly is a Stored procedure hierarchy?

This is where a SP contains within it a cell (vie EXECUTE) to 1 or more other SP's and within those SP's they may call one or more other SP's. 

DanC

Hi @EdCarden

I don't believe we have this feature in the tool, if you'd like to make a feature request please post on the forum here: https://redgate.uservoice.com/forums/94413-sql-prompt

EdCarden

DanC said:

Hi @EdCarden

I don't believe we have this feature in the tool, if you'd like to make a feature request please post on the forum here: https://redgate.uservoice.com/forums/94413-sql-prompt

Dan - Is this even possible within T-SQL? I've been scouring the net trying to find a way to do this and the best I could find is some code that would show you the first SP call within a SP and that's it.  I was fully expecting, when I started searching, that there'd be some built-in way/method to do this. 

DanC

Hi @EdCarden

I'll be honest  I have no idea, I think Stackoverflow would perhaps help for this question!

Sorry I can't be of more help!

EdCarden

FYI - I ended up answering my own question. I had previously tried to use SSMS's View Dependencies feature but I had selected the wrong option; Objects that Depend on SP_Name. When I switched to Objects on which SP_Name depends it gave me exactly what I was looking for.  I'm kind of shocked I didn't see this listed 9in my google search) as a way to get this. Now the question is, who does SSMS's View Dependencies feature do it, what T-SQL does it use; and I am assuming that's how it's doing this. 

NOTE: The View Dependencies does not require any permissions that are above the dbo; no server level permissions. 

That said it would still be a nice feature to add to SQL Prompt with some addiotnal features/perks like being able to export to Word or even visio, gliffy or other popular diagraming apps. 

tslagter

SQL Dependency Tracker will do what you want.

EdCarden

tslagter said:

SQL Dependency Tracker will do what you want.

It can but it's over kill for what I needed. Clearly there's SQL Code for it b/c SQL Server Mgt Studio does it. I've just been shocked that no one that can find has posted what that is.  I've found several ways to get a single of direct dependency but nothing that iterates thru an entre set of dependencies like you get when you right -click in SSMS and select View Dependency.  SSMS also let's you see the dependency from bottom up to top down

tslagter

You can query it yourself, but it'll take a little work to get a full tree. These will work with stored procedures as well as tables:

https://stackoverflow.com/questions/22005698/how-to-find-all-the-dependencies-of-a-table-in-sql-server