Thursday, December 23, 2010

Drop all stored procedures from SQL Server database

Following SQL script drops all user stored procedures from a SQL Server database.

USE TestDatabase

DECLARE @CurrStoredProcedureName SYSNAME
DECLARE StoredProceduresCursor CURSOR FOR
SELECT name FROM sys.objects WHERE type = 'P'

OPEN StoredProceduresCursor
FETCH NEXT FROM StoredProceduresCursor INTO @CurrStoredProcedureName
EXECUTE('DROP PROCEDURE ' + @CurrStoredProcedureName)
FETCH NEXT FROM StoredProceduresCursor INTO @CurrStoredProcedureName

CLOSE StoredProceduresCursor
DEALLOCATE StoredProceduresCursor


Replace database name with your database name in the above script. Mainly we are doing following two things:

  1. Get all the user stored procedures in a CURSOR.

  2. Loop through the cursor, build a DROP PROCEDURE query for each stored procedure and execute it.

No comments: