A better way to understand stored procedures

Question

I am trying to decypher an absolute monstrosity of a stored procedure and i'm not having much luck. Are there any free tools that will help visualise the query or at least format the syntax into a more readable format ?

Any hints and tips are also welcome.

The type of database i am using is MS sql server 2005

Answer 1

If you are using SQL Server, you could look at the execution plan.

You can also use a tool like SQLInform to reformat your sproc if it's in bad shape.

Answer 2

Find all of the tables which are being used and draw yourself an ERD to better understand.

Answer 3

Have you got a copy of Visual Studio handy? Visual Studio highlights blocks of statements which can sometimes help to isolate a logical block. You can download the (free) Express Editions if you can spare the bandwidth.

If you want something more lightweight, there is a long list of products here.

For Stored Proc debugging tips.. I'd suggest the following:

1. Try and determine the use of declared variables - especially if they are referenced in various queries (add comments as you go, will help you piece it together)
2. See if you can copy out sections of the sproc, i.e. break it down into chunks you can more easily investigate
3. Isolate any dependencies (e.g. udfs) and ensure you understand what they contribute to the proc - sometimes the udf might give you an insight you couldn't otherwise glean

Answer 4

Often it can help to run the individual parts of the stored procedure manually. It takes some work, but will likely help.

Start by making the stored procedure into a standard sql by removing the sproc declaration and declaring and initializing each needed parameter.

Run an incrementally bigger part of the sproc, and inspect the results to understand what happens. Do this by printing variables or temp tables, or just by modifying output to the screen rather than a temp source.

And do make sure you document/split/rewrite what you find in the end - you don't want anyone else to have to do the same, right?

Answer 5

What is monstrous about it? Is it one enormous statement, many successive statements, or many conditional branches to statements? For very large complex statements, I think it can ease readability to pull subqueries out into CTEs. If there are many statements or conditional branches, it can be helpful to pull out units of processing to other sprocs that the monster sproc calls into.

Answer 6

I found an online SQL "prettifier" the other day in response to a similar query, this might be of use to you:

http://stackoverflow.com/questions/834309/align-microsoft-access-queries/

Answer 7

if there are lots of loops and/or IF/ELSE, add many "PRINT 'Loop/IF X.Y'", where X is a unique Loop/IF name/number of your choosing and Y is unique location in that Loop/IF. Then run the procedure from management studio. This will give you a quick idea of the running flow of the procedure.

You can expand on this idea, and output local variables and their values, row counts, etc. at various locations in the procedure to get a feel what is going on.

if you are unsure of how the run it manually because you don't know what parameters to use, capture the parameters. On the first line of the procedure insert into a log table:

INSERT INTO YourLogTable
    VALUES ('EXEC YourProcedureName @Param1='+COALESCE(''''+convert(varchar,@Param1)+'''','NULL')+', @Param2='+COALESCE(''''+convert(varchar,@Param2)+'''','NULL'))

you can then run your application to envoke that procedure and then cut/paste from the log and manually run that procedure.

Answer 8

I agree with Eppz. "Rolling your own" diagram will help you understand the procedure better. I have found this tool quite useful in conjunction: http://www.aivosto.com/visustin.html