This was the question asked by a member here http://www.dotnetspider.com/qa/ViewQuestion.aspx?QuestionId=59232
Though I have answered there itself, thought I would document it here also for future reference.
I) List all Stored procedures in a database:
Select * from sys.procedures where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'
Select * from sys.objects where type='p' and is_ms_shipped=0 and [name] not like 'sp[_]%diagram%'
Please note that I have added the 'NOT LIKE' part to get rid of stored procedures created during database installation.
II) Delete all [user created] Stored procedures in a database:
Select 'Drop Procedure ' + name from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'
This would list down all the user defined Stored procedure as 'Drop Procedure procedureName' in the result pane. Just copy-paste it into the query window and execute it once.
Technorati tags: SQL Server 2005