Droping 100 stored procedures without having to delete one by one.

Question

Post reply

Droping 100 stored procedures without having to delete one by one.

Mobashir Ahmed

SSC Veteran

Points: 209
More actions
August 24, 2006 at 12:07 pm

#367185

Hi All, can ant body guide me in the right direction, I have over 400 SP in my database and I have to delete 100 selected SP. Is there any TSql I can write that will drop these 100 SP. I have wrote a little script that will give me a list all the SP that I want to drop but I can not drop the SP because of the fact that I am assigning the SP name to the variable. I will appreciate if some body can guide me. Thanks
DECLARE
@sp_name varchar(100)
DECLARE
drop_sp CURSOR FOR
SELECT
sp.NAME
FROM
sys.procedures sp,sys.Tables tb
WHERE
LEFT(sp.NAME,CHARINDEX('_',sp.Name)-1)=tb.Name
AND
LEFT(sp.NAME,CHARINDEX('_',sp.Name)) not like ''
AND
sp.Name like 'Agreements_Delete'
ORDER
BY sp.Name
OPEN
drop_sp FETCH NEXT FROM drop_sp INTO @sp_name
WHILE
@@FETCH_STATUS = 0
BEGIN
select @sp_name as spname
DROP PROCEDURE spname
print @sp_name
FETCH NEXT FROM drop_sp INTO @sp_name
END
CLOSE
drop_sp
DEALLOCATE
drop_sp

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

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

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Ahmed,

You can build a dynamic sql statement for the exclusive drop statement and execute the same.

Hope this helps you.

Lucky

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 2

You need to use a wee bit of dynamic sql something like this should work

DECLARE

@sp_name varchar(128)

DECLARE

@sql nvarchar(4000)

DECLARE

drop_sp CURSOR FOR

SELECT

sp.NAME

FROM

sys.procedures sp,sys.Tables tb

WHERE

LEFT(sp.NAME,CHARINDEX('_',sp.Name)-1)=tb.Name

AND

LEFT(sp.NAME,CHARINDEX('_',sp.Name)) not like ''

AND

sp.Name like 'Agreements_Delete'

ORDER

BY sp.Name

OPEN

drop_sp FETCH NEXT FROM drop_sp INTO @sp_name

WHILE

@@FETCH_STATUS = 0

BEGIN

SET

@sql = 'DROP PROCEDURE ' +@sp_name

EXEC

sp_executesql @sql

print

@sp_name

FETCH

NEXT FROM drop_sp INTO @sp_name

END

CLOSE

drop_sp

DEALLOCATE

drop_sp

hth

David

P.s. you only need to post in one forum

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

This is what he means >>>

SELECT 'DROP PROCEDURE ' + sp.NAME As DropNames

FROM sys.procedures sp,sys.Tables tb

WHERE LEFT(sp.NAME,CHARINDEX('_',sp.Name)-1)=tb.Name

AND LEFT(sp.NAME,CHARINDEX('_',sp.Name)) not like ''

AND sp.Name like 'Agreements_Delete'

ORDER BY sp.Name

Run this query, the results of this query will be the drop statements for every procs you want to delete (assuming I didn't miss a typo). Just run that result set as a new script in query analyser and they'll all be gone... So make sure you back 'em up first by scripting 'em to a file... Experience speaking here .

Mobashir Ahmed SSC Veteran Points: 209 More actions · Answer 4

Thank you all for quick reply, I tried it and it worked. Thanks