Run all stored procedures in a database

Question

Run all stored procedures in a database

richardmgreen1

SSChampion

Points: 10356
More actions
February 12, 2015 at 6:47 am

#297653

Hi all
I'm looking for an easy way to run all stored procedures in a database that match a specified criteria.
Under normal curcumstances, I'd create a "master" procedure that would call each one in turn using the "EXECUTE" syntax.
As there will be around 140 procedures (there are a few more but they are used for different purposes), I'd like to try and execute them dynamically.
Is there any way of finding out which procedures match the pattern of "usp_merge_*" for the name and executing it?
Any help greatly appreciated.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

certainly possible, this is one of those situations where a cursor is probably appropriate, but there's a few questions:

what about parameters? do all your ~140 procs have no parameters/default parameters?

do they have logical dependencies/order of operations? ie are they supposed to run in a specific order? does sp_merge_companies have to run before sp_merge_CompanyOrders, stuff like that?

DECLARE @isql VARCHAR(2000)

DECLARE c1 CURSOR FOR

--cursor definition

SELECT 'EXEC ' + schema_name(schema_id) + '.' name

FROM sys.procedures

WHERE schema_name(schema_id) = 'dbo'

AND LEFT(name, 3) = 'sp_'

ORDER BY name

OPEN c1

FETCH next FROM c1 INTO @isql

WHILE @@FETCH_STATUS <> -1

BEGIN

PRINT @isql

--uncomment to actually execute!

--EXEC(@isql)

FETCH next FROM c1 INTO @isql

END

CLOSE c1

DEALLOCATE c1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

richardmgreen1 (2/12/2015)
Hi all
I'm looking for an easy way to run all stored procedures in a database that match a specified criteria.
Under normal curcumstances, I'd create a "master" procedure that would call each one in turn using the "EXECUTE" syntax.
As there will be around 140 procedures (there are a few more but they are used for different purposes), I'd like to try and execute them dynamically.
Is there any way of finding out which procedures match the pattern of "usp_merge_*" for the name and executing it?
Any help greatly appreciated.

Order doesn't matter at all here?

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

richardmgreen1 SSChampion Points: 10356 More actions · Answer 3

Hi Lowell

I'm hoping this is good news.....

There are no dependancies (i.e. no order they have to run in) and none of them have parameters.

I'm happy to use a cursor if that's the most efficient method.

richardmgreen1 SSChampion Points: 10356 More actions · Answer 4

Jeff Moden (2/12/2015)
Order doesn't matter at all here?

Hi Jeff

Not in the slightest (for now).

I'm just using it as a quick and dirty method to import a lot of data into a lot of tables.

I'll tidy up the process once we get the new server up and running (when a lot of changes will be happening anyway).