How to drop store procedure in SQL

Description:-

in  this article we will see about how to drop storeprocedure using sql query. here is simple demo example to remove all store procedure from Database. after also you can see a list of procedure removed from your database.

SQL Query:-

DECLARE @spName varchar(1000)

DECLARE spcurs cursOR
FOR SELECT [name] FROM sys.objects WHERE TYPE = 'p' -- p defines the stored procedure

OPEN spcurs
FETCH NEXT FROM spcurs INTO @spName
while @@fetch_status = 0
BEGIN
    exec('DROP PROCEDURE ' + @spName)
    print 'Deleted procedure -> '+ @spName
    FETCH NEXT  FROM spcurs INTO @spName
END
CLOSE spcurs

DEALLOCATE spcurs

Author : Umesh Patel

Related Posts