How to bulk delete stored procedures from a pattern

Question

Post reply

How to bulk delete stored procedures from a pattern

Edvard Korsbæk

SSCertifiable

Points: 5999
More actions
October 16, 2014 at 9:12 pm

#297525

Hi all!
I have app. 1000 stored procedures named:
Easy_UpdateSchemaTemp_17311230631246784
where the numbers from the underscore makes the diffeerence.
As the name says, they should be temporary, and they are not created anymore.
Is there an easy way to script the drop?
Or do i just have to start from one end untill finish?
Best regards
Edvard Korsbæk

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

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

happycat59 One Orange Chip Points: 29451 More actions · Answer 1

This should get you started...

select 'DROP PROCEDURE [' + routine_Schema

+ '].[' + routine_name + ']'

FROM INFORMATION_SCHEMA.ROUTINES

where routine_name like 'Easy_UpdateSchemaTemp%'

this will output the drop procedure statements you need. You can then paste them into a query window and run them. If you need to automate this, perhaps use a cursor with this select statement and execute each of the values returned by the cursor

homebrew01 SSC Guru Points: 55553 More actions · Answer 2

Learning the script is good, but if they are all grouped together alphabetically, you can view them on Object Explorer, then highlight them all at once and click delete.

If you script it, it's a good idea to generate a list first so you can verify beforehand.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

happycat59 (10/16/2014)
This should get you started...
select 'DROP PROCEDURE [' + routine_Schema
+ '].[' + routine_name + ']'
FROM INFORMATION_SCHEMA.ROUTINES
where routine_name like 'Easy_UpdateSchemaTemp%'
this will output the drop procedure statements you need. You can then paste them into a query window and run them. If you need to automate this, perhaps use a cursor with this select statement and execute each of the values returned by the cursor

Actually, no cursor is needed:

declare @SQLCmd nvarchar(max);

with base as (

select

schema_name(schema_id) SchemaName,

name as ProcName

from

sys.procedures prc

)

select @SQLCmd = stuff((select N'

drop procedure ' + b.SchemaName + N'.' + b.ProcName + N';

'

from

base b

where

b.ProcName like 'Easy_UpdateSchemaTemp%'

order by

b.ProcName

for xml path(''),TYPE).value('.','nvarchar(max)'),1,0,'');

select cast(@SQLCmd as xml); -- << clinking on the result in the grid opens the output in an XML tab in SSMS

-- exec sp_executesql @SQLCmd; -- << This, when uncommented, runs the code

homebrew01 SSC Guru Points: 55553 More actions · Answer 4

I do it this way. I run a script to generate the commands so I can verify I get the right ones. I script "Results to text", then copy-paste the results to a new window to run.

use MyDatabase

select 'use MyDatabase'

SELECT 'DROP PROCEDURE ' + o.name

FROM (select name from sys.objects where name like 'Easy_UpdateSchemaTemp_%') o

order by o.name

Edit: Ooops, happycat already suggested this.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

homebrew01 (10/18/2014)
I do it this way. I run a script to generate the commands so I can verify I get the right ones. I script "Results to text", then copy-paste the results to a new window to run.
use MyDatabase
select 'use MyDatabase'
SELECT 'DROP PROCEDURE ' + o.name
FROM (select name from sys.objects where name like 'Easy_UpdateSchemaTemp_%') o
order by o.name
Edit: Ooops, happycat already suggested this.

You can use my code the same way.