Advice on breaking down a large stored procedure

[McThuggets]

Hi everyone!

We have had a system in place for a number of years now that we want to rewrite. The crux of this system is this monstrous sql stored procedure that drives the system.

Over the years, various data fields and columns have been removed and added to the temporary tables in the SP but we have no record of which columns are actually required currently.

I have been given the task of going into this SP and noting down the tables and columns which are actually used... I am on day 5 of CTRL + F'ing my way through the stored procedure and noting down the fields being used. "I can finally see the finish line" I thought...

It has just dawned on me that we have about 6 other variations of this system for different clients that I will need to do all this for again.

There must be a programmatic way to do this but I am not really sure where to start. Any advice would be greatly appreciated.

* I did a quick google and found the SQL Server functions sp_depends and sys.dm_sql_referenced_entities() which may help. I will try to script something out with them tomorrow

 

[Areola Grande]

You may have tried this, but ChatGPT is remarkably good at this sort of thing. Maybe give it a try.

 

[moedeez]

You may have tried this, but ChatGPT is remarkably good at this sort of thing. Maybe give it a try.

Also a buddy has been using "Claude" I'm not sure if the spelling is right. He says he finds it quite good with code. The only limitation i can think of is the word count they can handle at a time.

 

[Dom]

Hi everyone!

We have had a system in place for a number of years now that we want to rewrite. The crux of this system is this monstrous sql stored procedure that drives the system.

Over the years, various data fields and columns have been removed and added to the temporary tables in the SP but we have no record of which columns are actually required currently.

I have been given the task of going into this SP and noting down the tables and columns which are actually used... I am on day 5 of CTRL + F'ing my way through the stored procedure and noting down the fields being used. "I can finally see the finish line" I thought...

It has just dawned on me that we have about 6 other variations of this system for different clients that I will need to do all this for again.

There must be a programmatic way to do this but I am not really sure where to start. Any advice would be greatly appreciated.

* I did a quick google and found the SQL Server functions sp_depends and sys.dm_sql_referenced_entities() which may help. I will try to script something out with them tomorrow

Does the SP have notation & a logical rhythm?

 

[Signor65]

Renaming any synonyms/aliases to more descriptive names, and then formatting the sp neatly via something like Redgate SQL formatter, can go a long way.

 

[zakk]

Yea, formatting and renaming and perhaps then trying to ask chatgpt or similar AI to write it in pseudo code could help a lot to just get the gist of the SP and then break it down into section that can be rewritten into individual processors.

Using a smaller data set and persisting the temp (in mem tables) could also be a good idea to actually look at the data in each step.

I would then go a draw a logical flow of this on a whiteboard until I fully grasp it.

and goodluck... inheriting code is always fun

EDIT - oh and pay it forward by writing documentation when you rewrite it. documentation is like sex, when it's good it's great and when it's bad it's better than nothing.