asked on

Tool to visualize SQL Procedures

I am working with a DB that has a lot of custom SP and I wanted to know if there is a tool out there that can help me with visualizing the SP.

I have both Visio and Lucidchart but from what I can see neither can take a stored procedure and turn it into a flowchart and identify all the tables and procedures the SP is calling.

Thank you,

Rich

Pavel Celba

This is almost impossible... Of course, simple stored procedure containing one query, e.g.

SELECT * FROM dbo.users

Open in new window

is easy to convert into Visio but that's not ordinary case obviously.
What you can do is to collect SP parameters in all cases, output data structure in some cases, maybe local variables declared and tables/views possibly accessed, and also you can obtain a list of syntax errors but that's almost everything... No tool can tell what was the intention of the developer, what the code should do etc.

In other words the information requested by you should be available prior to SP coding...

Each SP should contain some standard comment which provides the info requested for your purposes and which can then be used to generate some technical documentation..

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

This was posted in the SQL Topic Area. That doesn't specify any database product or version.

Assuming SQL Server.

I don't know of any tool that will give you what you want. Most database products have a way to view object dependency.

I found this for SQL Server:
https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/view-the-dependencies-of-a-stored-procedure?view=sql-server-ver15

These types of things aren't 100%. If the code uses dynamic SQL then they might not show up in the system views as a dependency but it is a place to start.