Generating a SP Dependency Tree

Question

Generating a SP Dependency Tree

Brian Ferguson

SSCommitted

Points: 1837
More actions
April 12, 2005 at 7:45 pm

#164076

Hi,
Does anyone in here know how to generate a stored procedure dependency tree?

For example, SP1 calls SP2 which calls SP3 and SP4. SP5 (stand alone)
I need something like
SP    CalledBy Level
SP1     None      0
SP2      SP1       1
SP3      SP2        2
Sp4      SP2        2
SP5     None        0
.
.
Any one got any ideas?
Thanks
Brian
bferguson@nc.rr.com.nospam

Viewing 15 posts - 1 through 15 (of 15 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

There are no 100% guaranteed ways to do this, except the old-fashioned traditional roll-up-your-sleeves-and-get-dirty method.

Audit the code, document the flow of calls and store the document in a safe place, preferrably in a source version control environment. If you have access to a RDBMS modelling tool, this is also a place where it could be documented, along with the rest of the model.

/Kenneth

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 2

Take a look in Enterprise Manager, right-click a procedure and select All Tasks, Display Dependencies. Uncheck the box that says "Show first level dependency only". You should get a list similar to what you are looking for. This list is built using information in the system table dbo.sysdepends.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Brian Ferguson SSCommitted Points: 1837 More actions · Answer 3

Thanks Chris, that's what I was looking for. I could not remember where this info was stored.

brian

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

sysdepends is broken, has always been broken and is not 100% accurate.

If you want to be thorough, and absolutely sure you get everything right, and not missing out on anything, the only way to do this is by manual inspection of the code.

You cannot trust sysdepends.

/Kenneth

Brian Ferguson SSCommitted Points: 1837 More actions · Answer 5

Thanks Kenneth,

That is part of the reason I was looking for something (sysdepends as Chris suggested, should work great).

Unfortunately, the code this client has written has less than stellar documentation. I needed a method to find out exactly what is in the databases.

Thanks

Brian

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

sysdepends has always been broken (it's part of the Sybase legacy code). However I believe that EM does it differently (like so many other functions) and is correct.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Brian Ferguson SSCommitted Points: 1837 More actions · Answer 7

EM uses the sp_MSdependencies stored proc. I'm testing with that now.

Thanks everyone

Brian

noeld SSC Guru Points: 96590 More actions · Answer 8

sp_MSdependencies does uses sysdepends, therefore garbage in ...

* Noel

YSLGuru SSC-Insane Points: 21656 More actions · Answer 9

Actually there is a piece of code that will show Stored Procedure dependencies and its available at this website. The name of the SP is funProcWithoutComments and you can find the details for it at the below URL.

http://www.sqlservercentral.com/columnists/spopovski/viewingthehierarchyofstoredprocedures.asp

This SP does not use the SYSDEPENDS table.

Ed

Kindest Regards,

Just say No to Facebook!

noeld SSC Guru Points: 96590 More actions · Answer 10

Unfortunately that procedure has this limitations:

Cannot handle more than 4000 chars in the sp body
if the sp is splitted i more than one row on syscomments it won't work
has to be adjusted to include views, tables, and functions -- doable but it needs a good bit of effort

The best way i believe would be a perl script that used in combination with the actual sysobjects info parse out the code!

Just my $0.02

* Noel

Jeff Moden SSC Guru Points: 1004175 More actions · Answer 11

When you have 0% to start with and the task is large, an 80 or 90% solution would probably be pretty useful as a start.

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

Jomy John Thomas Mr or Mrs. 500 Points: 550 More actions · Answer 12

Hi

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

StmtText

StmtId

NodeId

Parent

PhysicalOp

LogicalOp

Argument

DefinedValues

EstimateRows

EstimateIO

EstimateCPU

AvgRowSize

TotalSubtreeCost

OutputList

Warnings

Type

Parallel

EstimateExecutions

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.

Regards,

Jomy John Thomas

Your Guide to Admin and Design

Brian Ferguson SSCommitted Points: 1837 More actions · Answer 13

Thanks all for your suggestions, This give me alot more to go on than I originally had.

brian

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 14

Not necessarily (imo)

It might lead you to accept the 80-90% as 'good enough', which it isn't.

Or worse, it might lead you (or someone else) to think that it's actually 100% accurate. It's better to bite the bullet and do it right the first time.

Parsing through the code et al might also seem tempting, but then you have to get your hands of some pretty nifty code (which there may be out there, sure) that also can sift through object references in comments etc.

If you have any dynamic SQL in there anywhere, then parsing won't work either....

The bottom line is that you can't avoid reviewing the actual code in any case, so why not just do that from the start..?

just my .02 of course

/Kenneth