DROP all stored procedures

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[_SPLOG_DropProcs]
AS 
 BEGIN
--TO BE USED TO DROP STORED PROCEDURES CREATED WITH SPLOG. Requires the SPLOG table to be intact..


 DECLARE @TEMP TABLE
 (
 ID INT IDENTITY(1, 1),
 ROUTINE_NAME VARCHAR(100)
 )


 INSERT INTO @TEMP ( Routine_name )
 SELECT DISTINCT
 ROUTINE_NAME
 FROM INFORMATION_SCHEMA.ROUTINES a
 JOIN dbo.SPLOG b ON CONVERT(VARCHAR, LOWER(a.ROUTINE_NAME)) = REPLACE(CONVERT(VARCHAR, LOWER(spname)), CHAR(10), '')
 WHERE ROUTINE_NAME NOT LIKE '%SPLOG%'
 AND routine_definition LIKE '%CREATE PROC%'
    

 DECLARE @spname VARCHAR(100)
 DECLARE @counter INT
 SET @counter = 1
 DECLARE @max INT
 SET @max = ( SELECT MAX(id)
 FROM @TEMP
 )
 WHILE @counter <= @max
 BEGIN
 SET @spname = ( SELECT routine_name
 FROM @TEMP
 WHERE id = @counter
 )
 EXEC ( 'DROP PROCEDURE ' + @spname
 )
 SET @counter = @counter + 1
 END
 

 END