Technical Article

DROP all stored procedures

,

This code has specifically been written to complement this: http://www.sqlservercentral.com/scripts/AutoGenerate++++++parameters/64760/

to accomodate the dropping and recreation of stored procs should the db structure change...

Modification of the code can be done to accomodate the dropping of all stored procedures and functions (use drop function and take out create proc)

 

have fun...

 

Chris

 

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

Rate

5 (2)

Share

Share

Rate

5 (2)