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'
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.

About Ed Jones

Ed is a .NET and Integration architect for Avanade in the Twin Cities. Contact Ed

What do you think?

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: