Analyzing a huge ( more than 10600 lines )stoc proc using cte

Question

Post reply

Analyzing a huge ( more than 10600 lines )stoc proc using cte

dany

SSC Enthusiast

Points: 112
More actions
November 8, 2022 at 9:30 pm

#4110233

Hello commuty,
Need your help, your support and proposition!
I'm analysis a huge stoc proc in sql server including a list of cte , but it's very complicated to understand the differents dependencies,
Are there any tools or code that help to display a list of tables used in each cte !
Or to show a query designe with a list of tables and filters ?
Or a code to decrypt this code ?
In advance thanks a lot,

Viewing 4 posts - 1 through 3 (of 3 total)

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

ratbak Ten Centuries Points: 1358 More actions · Answer 1

SSMS can tell you dependencies for the procedure as a whole.

At the CTE level, as far as I know, that will require a diligent data engineer.

Be alert to the use of views vs. tables. Are any selecting from table-valued functions?

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 2

Peel one potato at a time. Like Ratbak suggests above, it will require a "Diligent Data Engineer". One of the best things to make as you go along, is a simple, functional flowchart. It might take a whole wall in your cube but you're going to need it to understand both the flow and the "gazintas". Don't put it off... start drawing it and keep it up as you go. You might even have to make sub-flowcharts for each queery. It'll be worth it.

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

Execute the sproc in PlanExplorer ( free tool ) to record its full execution plan en explore the marvels of that tool.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me