stored procedure structure

Question

I've inherited a legacy system with lots (160+) of stored procedures. I'm looking for a way to create a diagram that would

• show which of the procedures are called by other procedures
• show which part of the C# code uses which stored procedures
• group the procedures by the parts of the system in which they are used

Is there a tool that does this? Or would help me to perform the task?

Answer 1

DBScribe and DOxygen may help you.

Answer 2

Red Gate's SQL Dependency Tracker can handle your first item.

As for your second item, your only real option there is doing a Find In Files (control-shift-F by default) and searching your solution for that stored procedure name. You'll have to do your own analysis as to where that string actually gets used, though.

For the third, there are no tools that I'm aware of that can produce such a broad overview.

Answer 3

I would add some rudimentary logging to your procedures and create logs of procedure utilisation by business process.

So if your app manages fruit sales POS.

Buy and orange. Save Log

Buy an Apple. Save log.

Etc...

This way you find out not only which are used but when and with what.