This is a quite simple task to do with cursor because we need to execute command DROP PROCEDURE. So we create a cursor, fetch all names of stored procedure and drop them.
Here’s the script that implement the above logic.
Update 2015-11-26: The original script with SELECT name FROM sys.objects … works well if you have only the dbo schema. To delete all stored procedures in all schemas we need to use two-part objects names schema.object.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DECLARE @spname sysname; DECLARE SPCursor CURSOR FOR SELECT SCHEMA_NAME(schema_id) + '.' + name FROM sys.objects WHERE type = 'P'; OPEN SPCursor; FETCH NEXT FROM SPCursor INTO @spname; WHILE @@FETCH_STATUS = 0 BEGIN EXEC('DROP PROCEDURE ' + @spname); FETCH NEXT FROM SPCursor INTO @spname; END CLOSE SPCursor; DEALLOCATE SPCursor; |
If we change the object type in first WHERE clause (for example, ‘FN’ for scalar functions) and use appropriate command in EXEC statement (DROP FUNCTION in this case) we can delete all database objects of another type. Full list of object types can be foundĀ here