Need a list of stored procedures that execute multiple stored procedures

Question

Need a list of stored procedures that execute multiple stored procedures

Lord Slaagh

SSCertifiable

Points: 5745
More actions
December 2, 2024 at 9:38 pm
Go to Answer
#4492360

Hello SSC!
I hope you all had a happy and safe holiday!
Apologies if this post is confusing...
I have a bunch of procs that call an index rebuild proc multiple times based on column name. Basically, this is all hardcoded (I did not write this :)).
I tried a bunch of scripts to identify the "exec" statement and the "proc name". It helped, but it seems there is one more proc that has 27 executions that I cannot find. This seems to be my only clue since I fixed all other procs in my query.
Is there a query that returns a list of procs with a specified number of execs in the proc?
Any assistance would be greatly appreciated.
Dave
The are no problems, only solutions. --John Lennon
Phil Parkin

SSC Guru

Points: 246970
More actions
December 3, 2024 at 9:52 am
Answer
#4492551

Are you saying that you do not know the name of the proc that was executed 27 times (and if so, I'd be interested in knowing what you are looking at to determine this), or that you do not know the name of the proc that called it 27 times? Could something else have called it? (SSIS package, Agent job, etc)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 1

Does example #7 in the article at the following link do it for you? You can add criteria to only consider stored procedures.

https://www.mssqltips.com/sqlservertip/2999/different-ways-to-find-sql-server-object-dependencies/

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

frederico_fonseca SSCoach Points: 16160 More actions · Answer 2

and you also need to consider.

calls using dynamic SQL - will not show up as dependencies
SQL Agent jobs - both T-SQL on them, and command line/SSIS packages that may be doing the call directly
code in other databases that execute the procs (again they won't show up on dependencies)

might be good to change those index rebuild procs to log the call stack and other details onto a table so you can investigate.

Lord Slaagh SSCertifiable Points: 5745 More actions · Answer 3

Hello SSC,

I found it. The execution was in an ETL job that I haven't used before. If anyone else has this issue, try querying the SSISDB. This is how I found it.

SELECT * FROM [catalog].[internal_executables] WHERE [name] LIKE '%<your_stored_procedure_name>%';

Thank you all very much for your responses. I liked all of your posts; I hope that helps.

Happy Holidays!

The are no problems, only solutions. --John Lennon

Lord Slaagh SSCertifiable Points: 5745 More actions · Answer 4

This is very helpful for future searches. Thank you Frederico!

This reply was modified 1 day, 6 hours ago by Lord Slaagh.

The are no problems, only solutions. --John Lennon