Mahmoud ElMansy: knowledge meant to be free

T-SQL Script To Drop all Stored Procedures

Early today i faced issue of deleting all stored procedure in Backup database .

The normal action is to open SQL Mangement Studio and try to delete them the Problem in this solution
That you can’t select all stored procedure and delete them on one click

You must selecte the stored procedures one by one to perform this action

The solution is to make T-SQL script to do this for you.

The following script will get names of all stored procedure with out system stored procedure .

-- type='P' Means Stored procedure
SELECT 'drop proc '+name FROM sys.objects WHERE
TYPE='P' AND name NOT IN(SELECT name FROM sys.objects
WHERE name LIKE '%sp_%')