Sometimes you need to mass drop stored procedures in a database. Well i needed a convenient way to do delete all stored procs starting with the word ‘asp’ on my local DB. The following codes did the job really well:
select Name + ',' from sys.objects where Type = 'P' and name like '%asp%'
This will return you a list of sprocs separated by a comma which you can copy and paste into the next code, as follows:
drop procedure aspnet_Setup_RestorePermissions,
aspnet_Setup_RemoveAllRoleMembers,
aspnet_WebEvent_LogEvent,
aspnet_Personalization_GetApplicationId,
aspnet_Users_DeleteUser,
aspnet_AnyDataInTables,
aspnet_Applications_CreateApplication,
aspnet_Membership_GetUserByName,
aspnet_Membership_UpdateUserInfo
So instead of dropping each stored procedure manually, you can drop many stored procedures at once using this technique.