Wednesday, October 25, 2006

Listing / Dropping ALL stored procedures from a database in SQL Server 2005

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%'

OR

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:

2 comments:

Anonymous said...

This what exactly what I was looking for. It worked fine at the beginning. Currently it's not working for me anymore and I don't know why..

Anonymous said...

..removing "WHERE [type] = 'P'" helped