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.

Advertisements

About Ed Jones

Ed is a Connected Systems and .NET Specialist for RBA in the Twin Cities. Contact Ed

What do you think?

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: