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

• Mobashir Ahmed

  SSC Veteran

  Points: 209

  More actions

  August 24, 2006 at 12:08 pm

  #170096

  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

   

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  August 24, 2006 at 12:34 pm

  #656686

  Question answered here :

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