Is there a tool to build a relationship tree of the stored procedures and functions used in SQL Server 2008?

Question

I have a legacy data-driven application with an extremely complicated data model (and hundreds of SQL stored procedures and functions with no clear delineation of responsibility) in SQL Server 2008.

We have both web app and reporting stored procedures and they are supposed to live side-by-side but there are examples where this rule wasn't properly respected. It's completely undocumented.

After some recent overhauling, there are several [maybe] deprecated stored procedures that can go, but due to the confusion in the data model, it's difficult to determine "who uses what".

I'd love to know if there's a tool or strategy available that can help me model the dependencies of each of the stored procedures and functions in the database (since there are multiple layers calling one another).

To determine the dependencies manually is simply too much work - there are several hundred stored procedures alone, and nearly as many functions.

Is anyone aware of a solution that can crawl through the dependencies and provide an organized (or even graphical) visualization of these objects?

Thanks!

Answer 1

I use EMS SQL Manager Lite (free) for that (and more than that). When you open a stored proc/function, there's a Dependencies tab that shows what you want.