If you need to delete all the user creation stored procedure in SQL server database. You can use the following TSQL code snippet.
DECLARE @spName VARCHAR(128) DECLARE @SQL VARCHAR(500) SELECT @spName = ( SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name] ) WHILE @spName is not null BEGIN SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@spName) +']' EXEC (@SQL) PRINT 'Deleted Stored Procedure: ' + @spName SELECT @spName = (( SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name] ) END GO
The sysobjects table contains all the non-system stored procedures created by user. We can easily identify the record by specifying the type which has a type of P that indicate it is a stored procedure type.