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:12 pm

#119336

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

Bhushan Default port Points: 1427 More actions · Answer 1

Your Drop statement should be dynamic... EXEC ('Drop Procedure '+ @spname)

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

Question answered here :

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=149&messageid=303903

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 3

DECLARE @sp_name varchar(100),

@s_Cmd nvarchar(255)

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 @s_Cmd =N'Drop procedure ' + ltrim(rtrim(@sp_name))

exec(@s_Cmd)

print @sp_name

FETCH NEXT FROM drop_sp INTO @sp_name

END

CLOSE drop_sp

DEALLOCATE drop_sp

Steve Sawyer Valued Member Points: 59 More actions · Answer 4

There is a utility for SQL Server available called TOAD which will allow normal windows selection behavior such as Ctrl-Click and Shft-Clicks. Selected objects can then be deleted.