How to drop all stored procedures at once in SQL Server database

Question

Currently we use separate a drop statements for each stored procedure in the script file     IF EXISTS  SELECT   FROM sys objects WHERE object id   OBJECT ID N  dbo   MySP         AND type in  N P   N PC      DROP PROCEDURE  dbo   MySP     Is there a way to drop them all at once  or maybe in a loop

User · Accepted Answer

Something like  Found at Delete All Procedures from a database using a Stored procedure in SQL Server    Just so by the way  this seems like a VERY dangerous thing to do  just a thought     declare  procName varchar 500  declare cur cursor   for select  name  from sys objects where type    p  open cur fetch next from cur into  procName while   fetch status   0 begin     exec  drop procedure       procName            fetch next from cur into  procName end close cur deallocate cur

User · Answer

ANSI compliant  without cursor  PRINT   1 a  Delete stored procedures     CONVERT  VARCHAR 19   GETDATE    121    GO DECLARE  procedure NVARCHAR max  DECLARE  n CHAR 1  SET  n   CHAR 10  SELECT  procedure   isnull   procedure    n          DROP PROCEDURE      schema name schema id            name       FROM sys procedures  EXEC sp executesql  procedure PRINT   1 b  Stored procedures deleted     CONVERT  VARCHAR 19   GETDATE    121    GO

User · Answer

Try this   DECLARE  sql NVARCHAR MAX    N     SELECT  sql    N DROP PROCEDURE dbo       QUOTENAME name         FROM sys procedures WHERE name LIKE N spname   AND SCHEMA NAME schema id    N dbo    EXEC sp executesql  sql

User · Answer

I would prefer to do it this way    first generate the list of stored procedures to drop by inspecting the system catalog view   SELECT  DROP PROCEDURE      SCHEMA NAME p schema id            p NAME        FROM sys procedures p    This generates a list of DROP PROCEDURE statements in your SSMS output window   copy that list into a new query window  and possibly adapt it   change it and then execute it   No messy and slow cursors  gives you the ability to check and double-check your list of procedure to be dropped before you actually drop it

User · Answer

To get drop statements for all stored procedures in a database SELECT  DROP PROCEDURE          F NAME       FROM SYS objects AS F where type  P

User · Answer

Try this  it work for me  DECLARE  spname sysname  DECLARE SPCursor CURSOR FOR SELECT SCHEMA NAME schema id          name FROM sys objects WHERE type    P   OPEN SPCursor  FETCH NEXT FROM SPCursor INTO  spname  WHILE   FETCH STATUS   0 BEGIN EXEC  DROP PROCEDURE      spname   FETCH NEXT FROM SPCursor INTO  spname  END CLOSE SPCursor  DEALLOCATE SPCursor

User · Answer

Click on Stored Procedures Tab  Press f7 to Display All Stored Procedures  Select All Procedure By Ctrl   A except System Table  Press Delete button and Click OK      You can Delete Table as well as View in same manner

User · Answer

create below stored procedure in your db from which db u want to delete sp s   then right click on that procedure - click on Execute Stored Procedure    then click ok   create Procedure  dbo   DeleteAllProcedures  As  declare  schemaName varchar 500      declare  procName varchar 500  declare cur cursor for select s Name  p Name from sys procedures p INNER JOIN sys schemas s ON p schema id   s schema id WHERE p type    P  and is ms shipped   0 and p name not like  sp    diagram   ORDER BY s Name  p Name open cur  fetch next from cur into  schemaName  procName while   fetch status   0 begin if  procName  lt  gt   DeleteAllProcedures  exec  drop procedure      schemaName          procName  fetch next from cur into  schemaName  procName end close cur deallocate cur

User · Answer

DECLARE  DeleteProcCommand NVARCHAR 500   DECLARE Syntax Cursor CURSOR FOR SELECT  DROP PROCEDURE     p NAME FROM sys procedures p  OPEN Syntax Cursor  FETCH NEXT FROM Syntax Cursor  INTO  DeleteProcCommand  WHILE    FETCH STATUS   0  BEGIN  EXEC   DeleteProcCommand   FETCH NEXT FROM Syntax Cursor INTO  DeleteProcCommand  END  CLOSE Syntax Cursor  DEALLOCATE Syntax Cursor

User · Answer

DECLARE  sql VARCHAR MAX  SET  sql    SELECT  sql  sql  drop procedure    name       FROM sys objects WHERE type    p  AND  is ms shipped   0 exec  sql

User · Answer

ANSI compliant  without cursor  DECLARE  SQL national character varying MAX   SET  SQL      SELECT  SQL   SQL  N DROP PROCEDURE      REPLACE SPECIFIC SCHEMA  N     N        N        REPLACE SPECIFIC NAME  N     N        N      FROM INFORMATION SCHEMA ROUTINES   WHERE  1 1   AND ROUTINE TYPE    PROCEDURE   AND ROUTINE NAME NOT IN          dt adduserobject        dt droppropertiesbyid        dt dropuserobjectbyid        dt generateansiname        dt getobjwithprop        dt getobjwithprop u        dt getpropertiesbyid        dt getpropertiesbyid u        dt setpropertybyid        dt setpropertybyid u        dt verstamp006        dt verstamp007         sp helpdiagrams        sp creatediagram        sp alterdiagram        sp renamediagram        sp dropdiagram         sp helpdiagramdefinition        fn diagramobjects        sp upgraddiagrams       ORDER BY SPECIFIC NAME    -- PRINT  SQL EXEC  SQL     Without cursor  non-ansi compliant    DECLARE  sql NVARCHAR MAX    N      lineFeed NVARCHAR 2    CHAR 13    CHAR 10     SELECT  sql    sql   N DROP PROCEDURE     QUOTENAME SPECIFIC SCHEMA    N      QUOTENAME SPECIFIC NAME    N       lineFeed FROM INFORMATION SCHEMA ROUTINES  WHERE ROUTINE TYPE    PROCEDURE   -- AND SPECIFIC NAME LIKE  sp   RPT        AND ROUTINE NAME NOT IN         SELECT name FROM sys procedures WHERE is ms shipped  lt  gt  0       ORDER BY SPECIFIC NAME    -- PRINT  sql  EXECUTE  sql

User · Answer

By mixing the cursor and system procedure  we would have a optimized solution  as follow   DECLARE DelAllProcedures CURSOR FOR     SELECT name AS procedure name      FROM sys procedures  OPEN DelAllProcedures DECLARE  ProcName VARCHAR 100  FETCH NEXT  FROM DelAllProcedures INTO  ProcName WHILE   FETCH STATUS  -1 BEGIN      DECLARE  command VARCHAR 100      SET  command        SET  command  command  DROP PROCEDURE    ProcName     --DROP PROCEDURE   ProcName     EXECUTE   command      FETCH NEXT      FROM DelAllProcedures     INTO  ProcName END CLOSE DelAllProcedures DEALLOCATE DelAllProcedures

User · Answer

Try this   declare  procName varchar 500  declare cur cursor   for SELECT  DROP PROCEDURE      SCHEMA NAME p schema id            p NAME       FROM sys procedures p  open cur fetch next from cur into  procName while   fetch status   0 begin     exec   procName       fetch next from cur into  procName end close cur deallocate cur

User · Answer

I think this is the simplest way   DECLARE  sql VARCHAR MAX       SELECT  sql  sql  drop procedure    name       FROM sys objects  WHERE type    p  AND  is ms shipped   0  exec  sql

[sql] How to drop all stored procedures at once in SQL Server database?

Examples related to sql

Examples related to sql-server

Examples related to tsql