Deleting All Your SQL Server Stored Procedures at Once

This is a tip I picked up which you can categorize under the “fun with T-SQL” category…

Recently, I was looking to do some database cleanup and found that there’s no built-in way to delete every stored procedure in a database at once.  Most of my digging around found excellent articles and scripts on how to delete a single stored procedure, or to loop through a list using a cursor, but nothing really good for blowing them all away until I learned this little tidbit:  every proc in a database is stored in a system table called “information_schema.routines“.

Knowing this, it’s a pretty simple task to generate a list of DROP statements and execute them with a simple EXEC command.  Here’s the code:


DECLARE @dropSprocSql varchar(max)
SET @dropSprocSql =
(
SELECT ' DROP PROCEDURE ' + routine_schema + '.' + routine_name
FROM information_schema.routines
WHERE routine_schema = 'theSchema' AND routine_type = 'PROCEDURE'
FOR XML PATH('')
)
EXEC (@dropSprocSql)


Of course, you can only delete user-created procedures with this (which is nice considering the danger involved of deleting anything system-related). Also note the WHERE clause. In this example, I’m only looking for stored procedures for a particular schema, but you can open this up for all schemas just by removing that part of the where clause, and if you remove the “routine_type” portion as well, you would also remove your user-defined functions.